java 导出EXCEL
1、
API相关
官方主页http://poi.apache.org/index.html，
API文档http://poi.apache.org/apidocs/index.html
jar包
官方下载：http://poi.apache.org/download.html
2、（未验证）
Maven项目 pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.6</version> </dependency>
小提示：用maven引入依赖jar包的可能会遇到包引用不到的bug，但是maven依赖确实已经引入了，而且没有任何报错，但是只要一引用 org.apache.poi.hssf.usermodel下面的类
就会报错，报错内容为：Caused by: java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook。
3、
常用组件：
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
样式：
HSSFCellStyle cell样式
4、
代码
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.usermodel.HorizontalAlignment;
public class ExcelUtil {
public static final int DEFAULT_SIZE = 1000; //默认每次读取数据
public static final int DEFAULT_EXCEL_SIZE = 65500; //excel最大允许行数
/**
* @param clazz 传入class类
* @param response 响应
* @param dataList 导出的数据
* @param fileName 导出的文件名称
* @param title 导出数据标题
* @param length 每个sheet页显示的行数
*/
public static void derived(Class<?> clazz, HttpServletResponse response, List<Map<String, Object>> dataList, String fileName, String[] title, int length) {
//数据总条数
int recordNum = dataList.size();
//需要读取文件的次数
int readNum = (Math.floorMod(recordNum, DEFAULT_SIZE) ==0) ? Math.floorDiv(recordNum, DEFAULT_SIZE) : Math.floorDiv(recordNum, DEFAULT_SIZE) + 1;
int start = 1; // 读取开始行数(数据)
//sheet名字
String sheetName = "";
//sheet名字前、后缀
String sheetNamePrefix = "第";
String sheetNameSuffix = "页";
HSSFWorkbook wb = null;
//每个sheet页显示的行数
length = (length >= MAX_EXCEL_SIZE) ? MAX_EXCEL_SIZE : length;
for(int i = 0; i < readNum; i++) {
//每次读取的数据条数
int readCount = (i == (readNum - 1) ? Math.floorMod(recordNum, DEFAULT_SIZE) : DEFAULT_SIZE;
//sheet名字拼接
sheetName = sheetNamePrefix + String.valueOf(Math.floorDiv(start, length) + 1) + sheetNameSuffix;
//数据封装
String[][] values = new String[readCount][title.length];
for(int j = 0; j < readCount; j++) {
Map<String, Object> dataMap = dataList.get(j);
for(int k = 0; k < dataMap.size(); k++) {
values[j][k] = String.valueOf(dataList.get(j).get("" + k);
}
}
wb = getHSSFWorkbook(sheetName, title, values, wb, start, length);
start = start + DEFAULT_SIZE;
}
System.out.println("数据写入wb成功");
try{
setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
}catch (Exception e) {
System.out.println(clazz.getClass()+"导出文件失败");
}
}
/**
* @param sheetName sheet名称
* @param title 0行 标题
* @param values 每个单元个的值数组
* @param wb
* @param start 写入的起始行
* @param excel文件对象
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb, int start, int length) {
System.out.println("开始调用getHSSFWorkbook();从第" + start + "条数据写入名称为" + sheetName +"的sheet页");
//判断excel对象文件参数是否为空
if(wb == null) {
wb = new HSSFWorkbook();
}
//每个sheet页显示的行数
length = (length >= MAX_EXCEL_SIZE) ? MAX_EXCEL_SIZE : length;
//每页最多写65535行，除去首行，共有65534行数据
int page = Math.floorDiv(start, length); //最大值
int lineInPage = Math.floorMod(start, length); // 最小值
HSSFSheet sheet = null;
HSSFRow row = null;
if(lineInPage == 1) {
//创建sheet页对象
sheet = wb.createSheet(sheetName);
wb.setSheetOrder(sheetName, page);
//创建 0 行 - 标题
row = sheet.createRow(0);
//设置单元格的格式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
//单元格对象
HSSFCell cell = null;
//设置 0 行标题
for(int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
} else {
sheet = wb.getSheet(sheetName);
}
//根据单元格值的二维数组循环对单元格赋值
for(in = i = 0; i < values.length; i++) {
row = sheet.createRow(i + lineInPage);
for(int j = 0; j < values[i].length; j++) {
row.createCell(j).setCellValue(values[i][j]);
}
}
System.out.println("调用getHSSFWorkbook()结束");
return wb;
}
/**
* 把EXCEL文件对象返回到前端
* @param response response对象
* @param fileName 文件名
*/
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
//文件名转换
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
} catche (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
