时间：2017年07月06日星期四
说明：本文部分内容均来自慕课网。@慕课网：http://www.imooc.com
教学源码：无
学习源码：https://github.com/zccodere/s...
第一章：课程介绍
1-1 预备知识
基础知识
struts2框架（上传下载功能）
xml解析技术（导入模板）
JQuery EasyUI（前台美观）课程目录
实现方式
定制导入模版
导入文件
导出文件
总结
1-2 Excel解析的几种实现方式
读写Excel三种常用技术
POI
JXL
FASTEXCEL什么是POI
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能什么是HSSF
HSSF是Horrible SpreadSheet Format的缩写，也即“讨厌的电子表格格式”。通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件POI常用API
HSSF-读写Microsoft Excel格式档案的功能
XSSF-读写Microsoft Excel OOMXML格式档案的功
HWPF-读写Microsoft Word格式档案的功能
HSLF-读写Microsoft PowerPoint格式档案的功能
HDGF-读写Microsoft VIsio格式档案的功能iText
通过iText不仅可以生成PDF或rtf的文档，而且可以将XML、Html文件转化为PDF文件。下载iText.jar文件后，只需要在系统的classpath中加入iText.jar的路径，在程序中就可以使用iText类库了JXL
Java Excel是一个开源项目，可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。包括常见格式的设置：字体、颜色、背景、合并单元格等。POI与JXL对比：POI
效率高
操作相对复杂
支持公式，宏，图形图表，一些企业应用上会非常实用
能够修饰单元格属性
支持字体、数字、日期操作POI与JXL对比：JXL
效率低
操作简单
部分支持
能够修饰单元格属性，格式支持不如POI强大
支持字体、数字、日期操作FastExcel
FastExcel是一个采用纯Java开发的excel文件读写组件，支持Excel97-2003文件格式。FastExcel只能读取单元格的字符信息，而其它属性如颜色、字体等就不支持了，因此FastExcel只需要很小的内存。1-3 学习目标及概念介绍
实例练习
分别通过POI和JXL两种方式实现对Excel文件读写操作，通过代码体会两种方式异同相关概念
工作簿：相当于Excel文件
工作表：相当于Sheet页
行记录：Row
单元格Cell：一个单元格
第二章：实现原理
2-1 JXL创建Excel
创建一个名为myexcelone的maven项目，并添加JXL依赖，POM文件如下：
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.myimooc</groupId>
<artifactId>myexcelone</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>myexcelone</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- 支持 Excel操作 JXL -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!-- 支持Excel 操作 POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!-- 支持简化文件操作 -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!-- 支持高版本Excel操作 POI 的XSSF start -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<!-- 支持高版本Excel操作 POI 的XSSF end -->
<!-- 导入模块定制，支持xml解析 start -->
<!-- https://mvnrepository.com/artifact/org.jdom/jdom -->
<dependency>
<groupId>org.jdom</groupId>
<artifactId>jdom</artifactId>
<version>2.0.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.6</version>
</dependency>
<!-- 导入模块定制，支持xml解析 end -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
代码演示：
package com.myimooc.one;
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* 通过 JXL 创建 Excel 文件
* @author ZhangCheng on 2017-07-06
*
*/
public class JxlCreateExcel {
public static void main(String[] args) {
try {
create();
System.out.println("创建成功");
} catch (Exception e) {
System.out.println("创建失败，异常为：" + e);
}
}
/**
* 功能：创建 Excel 文件
* @throws Exception
*/
public static void create()throws Exception{
// 定义 数组存表头
String[] title = {"id","name","sex"};
// 定义Excel文件路径
File file = new File("d://jxl_test.xls");
// 创建文件
file.createNewFile();
// 创建工作簿
WritableWorkbook workBook = Workbook.createWorkbook(file);
// 创建sheet页
WritableSheet sheet = workBook.createSheet("sheet1", 0);
Label label = null;
// 第一行设置表头列名
for (int i = 0; i < title.length; i++) {
// 几列、几行、名称
label = new Label(i, 0, title[i]);
// 往sheet页中添加单元格
sheet.addCell(label);
}
// 追加数据
for (int i = 1; i < 10; i++) {
label = new Label(0,i,"a"+i);
sheet.addCell(label);
label = new Label(1,i,"user"+i);
sheet.addCell(label);
label = new Label(2,i,"男");
sheet.addCell(label);
}
// 写入数据
workBook.write();
// 释放资源
workBook.close();
}
}
2-2 JXL解析Excel
代码演示：
package com.myimooc.one;
import java.io.File;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
/**
* 通过 JXL 解析 Excel 文件
* @author ZhangCheng on 2017-07-06
*
*/
public class JxlReadExcel {
public static void main(String[] args) {
try {
read();
System.out.println("解析成功");
} catch (Exception e) {
System.out.println("解析失败，异常为：" + e);
}
}
/**
* 功能：解析 Excel 文件
* @throws Exception
*/
public static void read()throws Exception{
// 指定要解析excel文件的路径
File file = new File("d:/jxl_test.xls");
// 创建 WorkBook，并指定路径
Workbook workBook = Workbook.getWorkbook(file);
// 获取工作表sheet
Sheet sheet = workBook.getSheet(0);
// 获取数据-循环行
for (int i = 0; i < sheet.getRows(); i++) {
// 循环列
for (int j = 0; j < sheet.getColumns(); j++) {
// 获取每一个单元格
Cell cell = sheet.getCell(j, i);
System.out.print(cell.getContents()+"\t");
}
System.out.println();
}
// 释放资源
workBook.close();
}
}
2-3 POI创建Excel
在POM文件中添加如下相关依赖
<!-- 支持Excel 操作 POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!-- 支持简化文件操作 -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>代码演示：
package com.myimooc.one;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 通过 POI 创建 Excel 文件
* @author ZhangCheng on 2017-07-06
*
*/
public class PoiCreateExcel {
public static void main(String[] args) {
try {
create();
System.out.println("创建成功");
} catch (Exception e) {
System.out.println("创建失败，异常为：" + e);
}
}
/**
* 功能：创建 Excel 文件
* @throws Exception
*/
public static void create()throws Exception{
// 定义Excel文件路径
File file = new File("d:/poi_test.xls");
// 创建文件
file.createNewFile();
// 定义 数组存表头
String[] title = {"id","name","sex"};
// 创建Excel工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
// 创建工作表sheet
HSSFSheet sheet = workBook.createSheet();
// 创建第一行
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
// 将表头写入第一行
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 追加数据
for (int i = 1; i < 10; i++) {
HSSFRow nextRow = sheet.createRow(i);
HSSFCell cell2 = nextRow.createCell(0);
cell2.setCellValue("a"+i);
cell2 = nextRow.createCell(1);
cell2.setCellValue("user"+i);
cell2 = nextRow.createCell(2);
cell2.setCellValue("男");
}
// 将Excel内容写入文件
FileOutputStream stream = FileUtils.openOutputStream(file);
workBook.write(stream);
// 释放资源
stream.close();
workBook.close();
}
}
2-4 POI解析Excel
代码演示：
package com.myimooc.one;
import java.io.File;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 通过 POI 解析 Excel 文件
* @author ZhangCheng on 2017-07-06
*
*/
public class PoiReadExcel {
public static void main(String[] args) {
try {
read();
System.out.println("解析成功");
} catch (Exception e) {
System.out.println("解析失败，异常为：" + e);
}
}
/**
* 功能：解析 Excel 文件
* @throws Exception
*/
public static void read()throws Exception{
// 指定需要解析excel文件的路径
File file = new File("d:/poi_test.xls");
// 创建工作簿
HSSFWorkbook workBook = new HSSFWorkbook(FileUtils.openInputStream(file));
// 读取sheet页
HSSFSheet sheet = workBook.getSheetAt(0);
// 读取工作表中的数据
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i = firstRowNum; i < lastRowNum; i++) {
// 循环读取每一行数据
HSSFRow row = sheet.getRow(i);
// 获取当前行最后单元格列号
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
// 循环读取当前行中的每一个单元格
HSSFCell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.print(value+"\t");
}
System.out.println();
}
// 释放资源
workBook.close();
}
}
2-5 XSSF创建高版本Excel
在POM文件中添加以下相关依赖：
<!-- 支持高版本Excel操作 POI 的XSSF start -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<!-- 支持高版本Excel操作 POI 的XSSF end -->代码演示：
package com.myimooc.one;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.commons.io.FileUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 通过 POI 的 XSSF 创建高版本的 Excel 文件
* @author ZhangCheng on 2017-07-06
*
*/
public class PoiXssfCreateExcel {
public static void main(String[] args) {
try {
create();
System.out.println("创建成功");
} catch (Exception e) {
System.out.println("创建失败，异常为：" + e);
}
}
/**
* 功能：创建 Excel 文件
* @throws Exception
*/
public static void create()throws Exception{
// 定义Excel文件路径
File file = new File("d:/poisxxf_test.xlsx");
// 创建文件
file.createNewFile();
// 定义 数组存表头
String[] title = {"id","name","sex"};
// 创建Excel工作簿
XSSFWorkbook workBook = new XSSFWorkbook();
// 创建工作表sheet
XSSFSheet sheet = workBook.createSheet();
// 创建第一行
XSSFRow row = sheet.createRow(0);
XSSFCell cell = null;
// 将表头写入第一行
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 追加数据
for (int i = 1; i < 10; i++) {
XSSFRow nextRow = sheet.createRow(i);
XSSFCell cell2 = nextRow.createCell(0);
cell2.setCellValue("a"+i);
cell2 = nextRow.createCell(1);
cell2.setCellValue("user"+i);
cell2 = nextRow.createCell(2);
cell2.setCellValue("男");
}
// 将Excel内容写入文件
FileOutputStream stream = FileUtils.openOutputStream(file);
workBook.write(stream);
// 释放资源
stream.close();
workBook.close();
}
}
第三章：导入模版定制
3-1 生成规则
使用场景
用户想导入excel文件，在这之前，首先需要下载一个导入模版，按照模版规则填写数据。然后，在把这个excel文件导入到系统之中。根据业务的不同，导出的excel模版也是各种各样的。利用xml解析技术，确定模版样式
确定模版列
定义标题（合并单元格）
定义列名（表头）
定义数据区域单元格样式XML配置模版样式代码演示：
<?xml version="1.0" encoding="UTF-8"?>
<excel id="student" code="student" name="学生信息导入">
<colgroup>
<col index="A" width='17em'></col>
<col index="B" width='17em'></col>
<col index="C" width='17em'></col>
<col index="D" width='17em'></col>
<col index="E" width='17em'></col>
<col index="F" width='17em'></col>
</colgroup>
<title>
<tr height="16px">
<td rowspan="1" colspan="6" value="学生信息导入" />
</tr>
</title>
<thead>
<tr height="16px">
<th value="编号" />
<th value="姓名" />
<th value="年龄" />
<th value="性别" />
<th value="出生日期" />
<th value=" 爱好" />
</tr>
</thead>
<tbody>
<tr height="16px" firstrow="2" firstcol="0" repeat="5">
<td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->
<td type="string" isnullable="false" maxlength="50" /><!--姓名 -->
<td type="numeric" format="##0" isnullable="false" /><!--年龄 -->
<td type="enum" format="男,女" isnullable="true" /><!--性别 -->
<td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->
<td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->
</tr>
</tbody>
</excel>3-2 设置列宽及标题
在POM文件中添加xml解析相关依赖
<!-- 导入模块定制，支持xml解析 start -->
<!-- https://mvnrepository.com/artifact/org.jdom/jdom -->
<dependency>
<groupId>org.jdom</groupId>
<artifactId>jdom</artifactId>
<version>2.0.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.6</version>
</dependency>
<!-- 导入模块定制，支持xml解析 end -->代码演示：
// 获取项目根路径
String rootPath = System.getProperty("user.dir");
// 获取解析xml文件路径
String path = rootPath + "/src/main/resources/student2.xml";
System.out.println(path);
File file = new File(path);
// 解析xml文件
SAXBuilder builder = new SAXBuilder();
Document parse = builder.build(file);
// 创建excel
HSSFWorkbook wb = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = wb.createSheet("sheet0");
// 获取xml文件根节点
Element root = parse.getRootElement();
// 获取模版名称
String templateName = root.getAttribute("name").getValue();
int rownum = 0;
int column = 0;
// 设置列宽
Element colgroup = root.getChild("colgroup");
setColumnWidth(sheet,colgroup);
// 设置标题
Element title = root.getChild("title");
List<Element> trs = title.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
List<Element> tds = tr.getChildren("td");
// 创建一行
HSSFRow row = sheet.createRow(rownum);
// 设置样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
// 创建单元格
HSSFCell cell = row.createCell(column);
Attribute rowSpan = td.getAttribute("rowspan");
Attribute colSpan = td.getAttribute("colspan");
Attribute value = td.getAttribute("value");
if(value != null){
String val = value.getValue();
cell.setCellValue(val);
int rspan = rowSpan.getIntValue() - 1;
int cspan = colSpan.getIntValue() - 1;
// 设置字体
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");// 字体格式
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
//font.setFontHeight((short) 12);// 字体大小
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
}
}
rownum ++;
}3-3 设置表头
代码演示：
// 设置表头
Element thead = root.getChild("thead");
trs = thead.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
HSSFRow row = sheet.createRow(rownum);
List<Element> ths = tr.getChildren("th");
for (column = 0; column < ths.size(); column++) {
Element th = ths.get(column);
Attribute valueAttr = th.getAttribute("value");
HSSFCell cell = row.createCell(column);
if(valueAttr != null){
String value = valueAttr.getValue();
cell.setCellValue(value);
}
}
rownum ++;
}3-4 数据区域样式
代码演示：
// 设置数据区域样式
Element tbody = root.getChild("tbody");
Element tr = tbody.getChild("tr");
int repeat = tr.getAttribute("repeat").getIntValue();
List<Element> tds = tr.getChildren("td");
for (int i = 0; i < repeat; i++) {
HSSFRow row = sheet.createRow(rownum);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
setType(wb,cell,td);
}
rownum ++;
}本节完整代码如下
1.exce模版配置xml文件
<?xml version="1.0" encoding="UTF-8"?>
<excel id="student" code="student" name="学生信息导入">
<colgroup>
<col index="A" width='17em'></col>
<col index="B" width='17em'></col>
<col index="C" width='17em'></col>
<col index="D" width='17em'></col>
<col index="E" width='17em'></col>
<col index="F" width='17em'></col>
</colgroup>
<title>
<tr height="16px">
<td rowspan="1" colspan="6" value="学生信息导入" />
</tr>
</title>
<thead>
<tr height="16px">
<th value="编号" />
<th value="姓名" />
<th value="年龄" />
<th value="性别" />
<th value="出生日期" />
<th value=" 爱好" />
</tr>
</thead>
<tbody>
<tr height="16px" firstrow="2" firstcol="0" repeat="5">
<td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->
<td type="string" isnullable="false" maxlength="50" /><!--姓名 -->
<td type="numeric" format="##0" isnullable="false" /><!--年龄 -->
<td type="enum" format="男,女" isnullable="true" /><!--性别 -->
<td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->
<td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->
</tr>
</tbody>
</excel>2.CreateTemplate类
package com.myimooc.one;
import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jdom2.Attribute;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.input.SAXBuilder;
/**
* 创建 Excel 模版文件
* @author ZhangCheng on 2017-07-06
*
*/
public class CreateTemplate {
public static void main(String[] args) {
try {
create();
System.out.println("创建成功");
} catch (Exception e) {
System.out.println("创建失败，异常为：" + e);
e.printStackTrace();
}
}
/**
* 功能：创建 Excel 模版文件
* @throws Exception
*/
@SuppressWarnings("deprecation")
public static void create()throws Exception{
// 获取项目根路径
String rootPath = System.getProperty("user.dir");
// 获取解析xml文件路径
String path = rootPath + "/src/main/resources/student2.xml";
System.out.println(path);
File file = new File(path);
// 解析xml文件
SAXBuilder builder = new SAXBuilder();
Document parse = builder.build(file);
// 创建excel
HSSFWorkbook wb = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = wb.createSheet("sheet0");
// 获取xml文件根节点
Element root = parse.getRootElement();
// 获取模版名称
String templateName = root.getAttribute("name").getValue();
int rownum = 0;
int column = 0;
// 设置列宽
Element colgroup = root.getChild("colgroup");
setColumnWidth(sheet,colgroup);
// 设置标题
Element title = root.getChild("title");
List<Element> trs = title.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
List<Element> tds = tr.getChildren("td");
// 创建一行
HSSFRow row = sheet.createRow(rownum);
// 设置样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
// 创建单元格
HSSFCell cell = row.createCell(column);
Attribute rowSpan = td.getAttribute("rowspan");
Attribute colSpan = td.getAttribute("colspan");
Attribute value = td.getAttribute("value");
if(value != null){
String val = value.getValue();
cell.setCellValue(val);
int rspan = rowSpan.getIntValue() - 1;
int cspan = colSpan.getIntValue() - 1;
// 设置字体
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");// 字体格式
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
//font.setFontHeight((short) 12);// 字体大小
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
}
}
rownum ++;
}
// 设置表头
Element thead = root.getChild("thead");
trs = thead.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
HSSFRow row = sheet.createRow(rownum);
List<Element> ths = tr.getChildren("th");
for (column = 0; column < ths.size(); column++) {
Element th = ths.get(column);
Attribute valueAttr = th.getAttribute("value");
HSSFCell cell = row.createCell(column);
if(valueAttr != null){
String value = valueAttr.getValue();
cell.setCellValue(value);
}
}
rownum ++;
}
// 设置数据区域样式
Element tbody = root.getChild("tbody");
Element tr = tbody.getChild("tr");
int repeat = tr.getAttribute("repeat").getIntValue();
List<Element> tds = tr.getChildren("td");
for (int i = 0; i < repeat; i++) {
HSSFRow row = sheet.createRow(rownum);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
setType(wb,cell,td);
}
rownum ++;
}
// 生成excel导入模版
File templateFile = new File("d:/" + templateName + ".xls");
templateFile.delete();
templateFile.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(templateFile);
wb.write(stream);
stream.close();
}
/**
* 功能：设置单元格样式
* @param wb
* @param cell
* @param td
*/
@SuppressWarnings("deprecation")
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cellStyle = wb.createCellStyle();
if("NUMERIC".equalsIgnoreCase(type)){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNoneBlank(formatValue)?formatValue:"#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
}else if("STRING".equalsIgnoreCase(type)){
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
}else if("DATE".equalsIgnoreCase(type)){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
}else if("ENUM".equalsIgnoreCase(type)){
CellRangeAddressList regions =
new CellRangeAddressList(cell.getRowIndex(),
cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
// 加载下拉列表内容
DVConstraint constraint =
DVConstraint.createExplicitListConstraint(enumValue.split(","));
// 数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
/**
* 功能：设置工作表列宽
* @param sheet 工作表
* @param colgroup
*/
private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
List<Element> cols = colgroup.getChildren("col");
for (int i = 0; i < cols.size(); i++) {
// 获取每一列的设置
Element col = cols.get(i);
Attribute width = col.getAttribute("width");
// 宽度单位
String unit = width.getValue().replaceAll("[0-9,\\.]", "");
String value = width.getValue().replaceAll(unit, "");
int v = 0;
if(StringUtils.isBlank(unit) || "px".equals(unit)){
v = Math.round(Float.parseFloat(value) *37F);
}else if("em".endsWith(unit)){
v = Math.round(Float.parseFloat(value) *267.5F);
}
// 设置宽度
sheet.setColumnWidth(i, v);
}
}
}
第四章：文件导入
4-1 环境搭建
教学使用环境
Struts2
Jquery EasyUI
MySql数据库个人学习环境
Spring Boot
Jquery EasyUI
MySql数据库创建一个名为myexcelweb的项目，POM文件如下：
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.myimooc</groupId>
<artifactId>myexcelweb</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>myexcelweb</name>
<url>http://maven.apache.org</url>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.1.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 支持Excel 操作 POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!-- 支持简化文件操作 -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!-- 支持文件上传 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- 支持高版本Excel操作 POI 的XSSF start -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
</dependency>
<!-- 支持高版本Excel操作 POI 的XSSF end -->
<!-- 导入模块定制，支持xml解析 start -->
<!-- https://mvnrepository.com/artifact/org.jdom/jdom -->
<dependency>
<groupId>org.jdom</groupId>
<artifactId>jdom</artifactId>
<version>2.0.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.6</version>
</dependency>
<!-- 导入模块定制，支持xml解析 end -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
4-2 导入模版下载（上）
说明：由于代码量太大，这里仅展示部分页面效果。具体源码可到我github地址查看。
课程视频的第四章和第五章是纯代码讲解，我也不知道该怎样来组织需要展示的内容了。
后端结构图
前端结构图
4-3 导入模版下载（下）
部分代码：
package com.myimooc.myexcelweb.web.controller;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.myimooc.myexcelweb.domain.model.ImportData;
import com.myimooc.myexcelweb.domain.model.ImportDataDetail;
import com.myimooc.myexcelweb.domain.vo.Template;
import com.myimooc.myexcelweb.service.ImportDataDeatilService;
import com.myimooc.myexcelweb.service.ImportDataService;
import com.myimooc.myexcelweb.util.CreateTemplateUtils;
import com.myimooc.myexcelweb.util.DateUtils;
/**
* 数据导入相关 rest 接口
* @author ZhangCheng on 2017-07-08
*
*/
@RestController
public class ImportDataController {
private static Logger logger = LoggerFactory.getLogger(ImportDataController.class);
@Autowired
private ImportDataService importDataService;
@Autowired
private ImportDataDeatilService importDataDeatilService;
/**
* 功能：获取导入列表数据
*/
@RequestMapping("importdata-list")
public Object importdataList(){
Map<String,Object> respData = new HashMap<String,Object>();
List<ImportData> importDataList = importDataService.list();
respData.put("total", importDataList.size());
respData.put("rows", importDataList);
return importDataList;
}
/**
* 功能：获取导入数据模版
*/
@RequestMapping("importdata-templates")
public Object importdataTemplates(){
List<Template> list = new ArrayList<Template>();
Template t = new Template();
t.setTemplateId("student");
t.setTemplateName("student");
list.add(t);
return list;
}
/**
* 功能：数据导入
*/
@SuppressWarnings("deprecation")
@PostMapping("importdata-upload")
public Object importdataUpload(MultipartFile file){
if(null == file){
return "上传失败，文件为空";
}
try {
String fileName = file.getName();
String filePath = getClass().getClassLoader().getResource("config/excel/").getPath()+fileName;
File excelFile = new File(filePath);
FileUtils.writeByteArrayToFile(excelFile, file.getBytes());
ImportData importData = new ImportData();
Long importDataId = DateUtils.getTimeInstant();
importData.setId(importDataId);
importData.setImport_data_type("student");
importData.setImport_status(1+"");
importData.setImport_date(DateUtils.nowToString());
importDataService.save(importData);
// 创建工作簿
HSSFWorkbook workBook = new HSSFWorkbook(file.getInputStream());
// 读取sheet页
HSSFSheet sheet = workBook.getSheetAt(0);
// 读取工作表中的数据
int firstRowNum = 1;
int lastRowNum = sheet.getLastRowNum();
List<ImportDataDetail> importDataDetailList = new ArrayList<ImportDataDetail>();
for (int i = firstRowNum; i < lastRowNum; i++) {
// 循环读取每一行数据
HSSFRow row = sheet.getRow(i);
// 获取当前行最后单元格列号
int lastCellNum = row.getLastCellNum();
ImportDataDetail importDataDetail = new ImportDataDetail();
for (int j = 0; j < lastCellNum; j++) {
// 循环读取当前行中的每一个单元格
HSSFCell cell = row.getCell(j);
String value = "";
int type = cell.getCellType();
if(HSSFCell.CELL_TYPE_NUMERIC == type){
value = cell.getNumericCellValue() + "";
System.out.print(value+"\t");
}else{
value = cell.getStringCellValue();
System.out.print(value+"\t");
}
System.out.print(value+"\t");
switch(j){
case 0:
importDataDetail.setCol0(value);
case 1:
importDataDetail.setCol1(value);
case 2:
importDataDetail.setCol2(value);
case 3:
importDataDetail.setCol3(value);
case 4:
importDataDetail.setCol4(value);
case 5:
importDataDetail.setCol5(value);
case 6:
importDataDetail.setCol6(value);
}
importDataDetail.setDeal_status(1+"");
importDataDetail.setImport_id(importDataId);
importDataDetail.setId(DateUtils.getTimeInstant()+Math.round(DateUtils.getTimeInstant()));
}
System.out.println();
importDataDetailList.add(importDataDetail);
}
// 释放资源
workBook.close();
importDataDeatilService.save(importDataDetailList);
importData = importDataService.findOne(importDataId);
importData.setDeal_date(DateUtils.nowToString());
importData.setDeal_status(1+"");
importDataService.save(importData);
return "上传成功";
} catch (IOException e) {
e.printStackTrace();
}
return "上传失败";
}
/**
* 功能：下载导入数据模版
*/
@RequestMapping("download")
public void download(HttpServletRequest request,HttpServletResponse response,String templateId){
String fileName = "student.xls";
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+fileName);
String xmlPath = getClass().getClassLoader().getResource("config/excel/student.xml").getPath();
String filePath = getClass().getClassLoader().getResource("config/excel/").getPath();
File xmlFile = new File(xmlPath);
File excelFile = new File(filePath + fileName);
try {
CreateTemplateUtils.create(xmlFile,excelFile);
logger.info("创建成功：{}",excelFile.getName());
InputStream in = FileUtils.openInputStream(excelFile);
int b;
while((b=in.read())!= -1)
{
response.getOutputStream().write(b);
}
} catch (Exception e) {
logger.info("创建失败，异常为：{}",e);
e.printStackTrace();
}
}
}
第五章：列表数据导出
5-1 数据导出实现过程
文件导出实现过程
获取列表表头信息
获取符合查询条件的数据
生成Excel文件
5-2 获取数据类及方法
5-3 前台实现
这里是使用easyui进行前端开发。
5-4 后台实现
package com.myimooc.myexcelweb.web.controller;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.myimooc.myexcelweb.domain.model.Student;
import com.myimooc.myexcelweb.service.StudentService;
/**
* 学生信息相关 rest 接口
* @author ZhangCheng on 2017-07-08
*
*/
@RestController
public class StudentController {
private static Logger logger = LoggerFactory.getLogger(ImportDataController.class);
@Autowired
private StudentService studentService;
/**
* 功能：获取学生信息列表
*/
@RequestMapping("student-list")
public Object studentList(){
logger.info("获取学生信息");
Map<String,Object> respData = new HashMap<String,Object>();
List<Student> studentList = studentService.list();
respData.put("total", studentList.size());
respData.put("rows", studentList);
return respData;
}
/**
* 功能：导出学生信息列表为excel
*/
@RequestMapping("student-export")
public void studentExport(HttpServletRequest request,HttpServletResponse response,String templateId){
String fileName = "学生信息.xls";
String enfileName = "";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
try {
enfileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
response.setHeader("Content-Disposition", "attachment;filename="+enfileName);
List<Student> studentList = studentService.list();
// 定义 数组存表头
String[] title = {"编号","姓名","年龄","性别","出生日期","爱好"};
// 创建Excel工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
// 创建工作表sheet
HSSFSheet sheet = workBook.createSheet("学生信息");
// 创建第一行
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
// 将表头写入第一行
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 追加数据
for (int i = 1; i < studentList.size(); i++) {
Student student = studentList.get(i);
HSSFRow nextRow = sheet.createRow(i);
HSSFCell cell2 = nextRow.createCell(0);
cell2.setCellValue(student.getStunum());
cell2 = nextRow.createCell(1);
cell2.setCellValue(student.getStuname());
cell2 = nextRow.createCell(2);
cell2.setCellValue(student.getStuage());
cell2 = nextRow.createCell(3);
cell2.setCellValue(student.getStusex());
cell2 = nextRow.createCell(4);
cell2.setCellValue(student.getStubirthday());
cell2 = nextRow.createCell(5);
cell2.setCellValue(student.getStuhobby());
}
String filePath = getClass().getClassLoader().getResource("config/excel/").getPath();
File excelFile = new File(filePath + fileName);
try {
// 将Excel内容写入文件
FileOutputStream stream = FileUtils.openOutputStream(excelFile);
workBook.write(stream);
// 释放资源
stream.close();
workBook.close();
logger.info("创建成功：{}",excelFile.getName());
InputStream in = FileUtils.openInputStream(excelFile);
int b;
while((b=in.read())!= -1)
{
response.getOutputStream().write(b);
}
} catch (Exception e) {
logger.info("创建失败，异常为：{}",e);
e.printStackTrace();
}
}
}
第六章：课程总结
6-1 课程总结
课程总结
读写Excel几种常用技术
模版定制原理
文件导入导出
java
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。