谨以此文献给天上星
导出多个工作表的Excel
package com.*.utils;
import java.io.OutputStream;
import java.util.List;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import com.*.BaseException;
/**
*
* @Describe 导出XLS文件(可以创建多个工作表)
* @Author Sakura
* @Date 2019/3/28
*/
public class GradesXLSWithMultipleWorksheets {
private String path;
public GradesXLSWithMultipleWorksheets() {
super();
}
//构造函数获取保存excel路径
public GradesXLSWithMultipleWorksheets(String path) {
super();
this.path = path;
}
/**
* 各个参数(除fileName)的长度必须一致,clientNames必须不一样
* @param fileName //文件名
* @param dataLists
* @param columns
* @param clientNames
* @param infoColums
* @param conditionColumns
* @param lastColumns
* @param response
* @return
* @throws BaseException
*/
public void CreateExcel(String fileName, List<List<Object[]>> dataLists, List<String[]> columns,List<String> clientNames,
List<String> infoColums,List<String> conditionColumns, List<String[]> lastColumns,
HttpServletResponse response) throws BaseException {
//校验参数
verifyParam(dataLists, columns, clientNames, infoColums, conditionColumns, lastColumns);
//创建一个工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
//创建工作表
for (int i = 0; i < dataLists.size(); i++) {
createSheet(workBook, dataLists.get(i), columns.get(i), clientNames.get(i), infoColums.get(i),
conditionColumns.get(i), lastColumns.get(i));
}
//设置文件名并导出
export(workBook, fileName, response);
}
/**
* 校验参数
* @param dataLists
* @param columns
* @param clientNames
* @param infoColums
* @param conditionColumns
* @param lastColumns
*/
private void verifyParam(List<List<Object[]>> dataLists, List<String[]> columns,List<String> clientNames,
List<String> infoColums,List<String> conditionColumns, List<String[]> lastColumns) {
if (dataLists == null || columns == null || clientNames == null ||
infoColums == null || conditionColumns == null || lastColumns == null) {
throw new BaseException("参数有误:存在null值");
}
int collect = clientNames.stream().distinct().collect(Collectors.counting()).intValue();
if (clientNames.size() != collect) {
throw new BaseException("参数有误:工作表名称不能相同");
}
if (dataLists.size() > 0 &&
dataLists.size() == columns.size() &&
columns.size() == clientNames.size() &&
clientNames.size()== infoColums.size() &&
infoColums.size() == conditionColumns.size() &&
conditionColumns.size() == lastColumns.size()) {
return;
}
throw new BaseException("参数有误:各个参数的长度必须一致");
}
/**
* 创建工作表
* @param workBook
* @param dataList
* @param column
* @param clientName
* @param infoColum
* @param conditionColumn
* @param lastColumn
* @return
*/
private void createSheet(HSSFWorkbook workBook,List<Object[]> dataList, String[] column, String clientName, String infoColum,
String conditionColumn, String[] lastColumn) {
//创建一个工作表,名为:第一页
HSSFSheet sheet = workBook.createSheet(clientName);
//表头样式
HSSFFont headfont = workBook.createFont();
headfont.setFontName("宋体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
HSSFCellStyle headstyle = workBook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
// 设置单元格的宽度(0:表示第一行的第一个单元格,1:第一行的第二个单元格)
sheet.setColumnWidth((short) 0, 1500);
for (int i = 1; i < column.length; i++) {
sheet.setColumnWidth((short) i, 4500);
}
//第一行表头
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column.length-1));
HSSFRow caption = sheet.createRow(0);
caption.setHeight((short) 0x349);
caption.createCell(0);
HSSFCell captionCell = caption.createCell(0);
captionCell.setCellStyle(headstyle);
captionCell.setCellValue(clientName);
// 创建第二行(表头--制表信息、制表人)
HSSFRow secondRow = sheet.createRow((short) 1);
secondRow.setHeight((short) 400);
HSSFCell secondCell = secondRow.createCell(0);
secondCell.setCellValue(infoColum);
// 设置第二行标题样式
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, column.length - 1));
HSSFCellStyle secondStyle = workBook.createCellStyle();
secondStyle.setAlignment(HorizontalAlignment.RIGHT); // 水平靠右
secondCell.setCellStyle(secondStyle);
secondRow.setRowStyle(secondStyle);
// 创建第三行(表头--查询条件信息)
HSSFRow fourthRow = sheet.createRow((short) 2);
fourthRow.setHeight((short) 400);
HSSFCell fourthCell = fourthRow.createCell(0);
fourthCell.setCellValue(conditionColumn);
// 设置第三行标题样式
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, column.length - 1));
// 创建第四行(表头--各字段信息)
//创建一个单元格,从0开始
HSSFRow row = sheet.createRow((short) 3);
// 设置第四行标题样式
HSSFCellStyle fourthStyle = workBook.createCellStyle();
HSSFFont fourthFont = workBook.createFont();
fourthFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fourthStyle.setFont(fourthFont);
row.setRowStyle(fourthStyle);
//构造一个数组设置第一行之后的单元格
HSSFCell[] cell = new HSSFCell[column.length];
for (int i = 0; i < column.length; i++) {
cell[i] = row.createCell(i);
cell[i].setCellValue(column[i]);
cell[i].setCellStyle(fourthStyle);
}
// 最后一行
HSSFRow lastRow = sheet.createRow((short) dataList.size() + 4);
// 构造一个数组设置第一行之后的单元格
// 最后一行样式
HSSFCellStyle lastStyle = workBook.createCellStyle();
HSSFFont lastFont = workBook.createFont();
lastFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
lastStyle.setFont(lastFont);
lastRow.setRowStyle(lastStyle);
HSSFCell[] cell1 = new HSSFCell[lastColumn.length];
for (int i = 0; i < lastColumn.length; i++) {
cell1[i] = lastRow.createCell(i);
cell1[i].setCellValue(lastColumn[i]);
cell1[i].setCellStyle(lastStyle);
}
//获得从数据库中查询出来的数据
if (dataList != null && dataList.size() > 0) {
//循环list中的数据
for (int i = 0; i < dataList.size(); i++) {
Object[] objs = dataList.get(i);
HSSFRow dataRow = sheet.createRow(i + 4);
HSSFCell data[] = new HSSFCell[column.length];
for (int j = 0; j < column.length; j++) {
data[j] = dataRow.createCell(j);
String info = String.valueOf(objs[j]);
data[j].setCellValue((info == null) ? "" : info);
}
}
}
}
/**
* 设置文件名并导出
* @param workBook
* @param clientName
* @param response
*/
private void export(HSSFWorkbook workBook, String clientName, HttpServletResponse response) {
try {
//设置日期格式
String fileName = clientName + "-" + DateTimeUtil.getDateTimeStr(DateTimeUtil.yyyyMMddHHmmss) + ".xls";
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gbk"), "iso8859-1"));
OutputStream os = response.getOutputStream();
workBook.write(os);
os.flush();
} catch (Exception e) {
throw new BaseException("导出Excel失败");
}
}
}