java POI 导出excel 生成多个工作表 合并单元格等
有些地方用到了公司框架中的工具类,代码如下:
/**
* 导出excel
*/
@Override
public void exportExcel(HomeForm form) {
try {
List<String> titles = new ArrayList<String>();
Collections.addAll(titles, new String[]{"奖金信息"});
List<String> headers = new ArrayList<String>();
List<String> headers1 = new ArrayList<String>();
Collections.addAll(headers, new String[]{"大修名称","公司名称","部门名称","部门奖金","自主专项","队长预留","专业队","专业队指标","专业队基数","专业队结算","队长预留" });
Collections.addAll(headers1, new String[] {"工号","姓名","岗位","岗位系数","考核系数","奖金" });
Map<String, List<String>> headersMap = new HashMap<>();
Map<String, List<String>> colNamesMap = new HashMap<>();
headersMap.put("headers", headers);
headersMap.put("headers1", headers1);
List<String> colNames = new ArrayList<String>();
List<String> colNames1 = new ArrayList<String>();
Collections.addAll(colNames, new String[] {"overhaulname", "companyname", "departmentname","Q_D","Q_D_Z","Q_D_D","teamname","K_T","Q_T_J","Q_T","Q_T_D" });
Collections.addAll(colNames1, new String[] {"loginname", "realname", "overhaulpostname","gw_k","k_gw","q_m" });
colNamesMap.put("colNames", colNames);
colNamesMap.put("colNames1", colNames1);
StringBuilder sql = new StringBuilder();
StringBuilder sql1 = new StringBuilder();
sql.append("select overhaulid,overhaulname,companyname,departmentname,Q_D,Q_D_Z,Q_D_D,teamname,K_T,Q_T_J,Q_T,Q_T_D from Va_Overhual");
sql.append(" where 1 = 1 and overhaulname='" + form.getOverhaulname() + "' and companyid = '" + form.getCompanyid() + "' order by departmentname");
sql1.append("select overhaulid,loginname,realname,overhaulpostname,gw_k,k_gw,q_m from Va_Overhual2");
sql1.append(" where 1 = 1 and overhaulname='" + form.getOverhaulname() + "' and companyid = '" + form.getCompanyid() + "'");
List<Map<String, Object>> dataList = queryForList(sql.toString());
List<Map<String, Object>> dataList1 = queryForList(sql1.toString());
String fileFullName = "奖金信息_" + FileUtil.getUUID() + ".xls";
String fileDir = Constant.APP_REPORT_DIR(form.getCompanyid());
String filePath = Constant.APP_REPORT_PATH(form.getCompanyid(), fileFullName);
form.setFileFullName(fileFullName);
String[] sheetNames = new String[]{"奖金信息表", "成员奖金信息"};
form.setStatus(exportExcel(sheetNames,titles, headersMap, colNamesMap, dataList, dataList1, true, filePath));
} catch (Exception e) {
form.setStatus(0);
}
}
/**
* 判断导出excel是否成功
* @return
*/
private int exportExcel(String[] sheetNames, List<String> titles, Map<String, List<String>> headersMap, Map<String, List<String>> colNamesMap, List<Map<String, Object>> dataList, List<Map<String, Object>> dataList1, boolean showRowNum, String filePath) throws Exception {
//1:成功 0:失败
int status = 0;
int createStatus = createExcel(sheetNames, titles, headersMap, colNamesMap, dataList, dataList1, showRowNum, filePath);
if(createStatus == 1){
status = ExcelUtil.downloadExcel(filePath);
}
return status;
}
@SuppressWarnings("deprecation")
protected final static Logger LOGGER = Constant.getLogger(ExcelUtil.class);
/** 序号在数据集中的Key */
private static String ROW_NUM_KEY = "ipetRowNum";
/** 序号在数据集中的值 */
private static String ROW_NUM_NAME = "序号";
/** 背景颜色在数据集中的Key */
public static String BG_COLOR_KEY = "ipetBgColor";
/**
* 创建excel
*/
private int createExcel(String[] sheetNames, List<String> titles, Map<String, List<String>> headersMap, Map<String, List<String>> colNamesMap, List<Map<String, Object>> dataList, List<Map<String, Object>> dataList1, boolean showRowNum, String filePath) {
int status = 0;
try {
HSSFWorkbook workBook = createWorkBook(sheetNames, titles, headersMap, colNamesMap, dataList, dataList1, showRowNum);
File file = new File(filePath);
FileUtil.mkdir(file.getParent());
OutputStream out = new FileOutputStream(filePath);
workBook.write(out);
out.flush();
out.close();
status = 1;
} catch (Exception e) {
LOGGER.error("发生异常", e);
status = 0;
}
return status;
}
/**
* 创建工作簿
*/
private HSSFWorkbook createWorkBook(String[] sheetNames, List<String> titles, Map<String, List<String>> headersMap, Map<String, List<String>> colNamesMap, List<Map<String, Object>> dataList, List<Map<String, Object>> dataList1, boolean showRowNum) {
HSSFWorkbook workBook = new HSSFWorkbook();
for (int i = 0; i < sheetNames.length; i++) {
if (i == 0) {
addSheet(workBook, sheetNames[i], titles, headersMap.get("headers"), colNamesMap.get("colNames"), dataList, showRowNum, true);
}
if (i == 1) {
addSheet(workBook, sheetNames[i], titles, headersMap.get("headers1"), colNamesMap.get("colNames1"), dataList1, showRowNum, false);
}
}
return workBook;
}
/**
* 创建工作表
*/
private void addSheet(HSSFWorkbook workBook, String sheetName, List<String> titles, List<String> headers, List<String> colNames, List<Map<String, Object>> dataList, boolean showRowNum, boolean isMerge) {
HSSFSheet sheet = workBook.createSheet(sheetName);
//excel表格数据
Map<String, Object> firstDataMap = null;
if(dataList !=null && dataList.size() > 0){
firstDataMap = dataList.get(0);
}
//表头
if(headers != null && headers.size() > 0){
if (colNames != null && colNames.size() > 0 && headers.size() != colNames.size()) {
LOGGER.error("表头列数与定义数据列数不匹配");
}
if (firstDataMap != null && headers.size() > firstDataMap.size()) {
LOGGER.error("表头列数与数据列数不匹配");
}
}
if (colNames != null && colNames.size() > 0) {
if (firstDataMap != null && colNames.size() > firstDataMap.size()) {
LOGGER.error("定义数据列数与数据列数不匹配");
}
}
if (colNames == null || colNames.size() <= 0) {
colNames = new ArrayList<String>();
for (String colName : firstDataMap.keySet()) {
colNames.add(colName);
}
}
boolean hasRowNum = colNames.contains(ROW_NUM_KEY);
if(showRowNum && !hasRowNum){
colNames.add(0, ROW_NUM_KEY);
}
int[] colWidths = new int[colNames.size()];
//样式设置
//标题样式
HSSFFont titleFont = workBook.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontHeightInPoints((short)16);
HSSFCellStyle titleStyle = workBook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setWrapText(true);
//表头样式
HSSFFont headerFont = workBook.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)12);
HSSFCellStyle headerStyle = workBook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setWrapText(true);
//内容遍历写入
//表格标题部分
int titleRowSize = 0;
if(titles != null && titles.size() > 0){
for (int i = 0; i < titles.size(); i++) {
HSSFRow titleRow = sheet.createRow(titleRowSize);
HSSFCell cell = titleRow.createCell(0);
cell.setCellStyle(titleStyle);
Object cellValue = titles.get(i);
//设置标题文本
HSSFRichTextString text = new HSSFRichTextString(cellValue != null ? cellValue.toString() : "");
cell.setCellValue(text);
titleRow.setHeight((short)500);
//标题所在行和列
CellRangeAddress region = new CellRangeAddress(i, i, 0,colNames.size());
sheet.addMergedRegion(region);
//设置标题边框
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region, sheet, workBook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region, sheet, workBook);
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region, sheet, workBook);
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region, sheet, workBook);
titleRowSize++;
}
}
//表头部分
int headerRowSize = 0;
if(headers != null && headers.size() > 0){
if(showRowNum && !hasRowNum){
//添加序号
headers.add(0, ROW_NUM_NAME);
}
HSSFRow row = sheet.createRow(titleRowSize);
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
Object cellValue = headers.get(i);
HSSFRichTextString text = new HSSFRichTextString(cellValue != null ? cellValue.toString() : "");
cell.setCellValue(text);
//长度乘以2是为了解决纯数字列宽度不足会显示科学计数法问题, 乘以256得到的数据才是excel真实列宽
int colWidth = cellValue != null ? cellValue.toString().getBytes().length * 1 * 256 :"".toString().getBytes().length * 1 * 256;
//设置列宽
colWidths[i] = colWidths[i] > colWidth ? colWidths[i] : colWidth;
}
headerRowSize++;
}
//单元格内容样式
//字体样式
HSSFFont bodyFont = workBook.createFont();
bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bodyFont.setFontHeightInPoints((short) 11);
//单元格样式
HSSFCellStyle bodyStyle = workBook.createCellStyle();
bodyStyle.setFont(bodyFont);
bodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
bodyStyle.setWrapText(true);
//部门名称
String departmentname = "";
//不同部门个数
int number = 0;
//表格内容起始行数
int rowNumber = 2;
List<Integer> rowList = new ArrayList<>();
rowList.add(rowNumber);
//单元格内容部分
for (int i = 0; i < dataList.size(); i++) {
//创建内容行
HSSFRow dataRow = sheet.createRow(i + headerRowSize + titleRowSize);
//获取内容
Map<String, Object> dataMap = dataList.get(i);
//添加序号
if (showRowNum) {
dataMap.put(ROW_NUM_KEY, i + 1);
}
//样式
HSSFCellStyle rowStyle = bodyStyle;
//获取颜色
Object rowBgColor = dataMap.get(BG_COLOR_KEY);
if (!StringUtil.isNullOrEmpty(rowBgColor)) {
rowStyle = workBook.createCellStyle();
rowStyle.cloneStyleFrom(bodyStyle);
//设置颜色
int[] colors = ByteUtil.fromHexToArrayInt(rowBgColor.toString());
HSSFPalette customPalette = workBook.getCustomPalette();
HSSFColor color = customPalette.findSimilarColor(colors[0], colors[1], colors[2]);
//设置图案颜色
rowStyle.setFillForegroundColor(color.getIndex());
//设置图案样式
rowStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
}
for (int j = 0; j < colNames.size(); j++) {
HSSFCellStyle cellStyle = rowStyle;
Object cellBgColor = dataMap.get(colNames.get(j) + "_" + BG_COLOR_KEY);
if (!StringUtil.isNullOrEmpty(cellBgColor)) {
cellStyle = workBook.createCellStyle();
cellStyle.cloneStyleFrom(rowStyle);
int[] colors = ByteUtil.fromHexToArrayInt(rowBgColor.toString());
HSSFPalette customPalette = workBook.getCustomPalette();
HSSFColor color = customPalette.findSimilarColor(colors[0], colors[1], colors[2]);
rowStyle.setFillForegroundColor(color.getIndex());
rowStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
}
HSSFCell cell = dataRow.createCell(j);
cell.setCellStyle(bodyStyle);
Object cellValue = "";
try {
cellValue = dataMap.get(colNames.get(j));
} catch (Exception e) {
LOGGER.error("发生异常", e);
}
HSSFRichTextString text = new HSSFRichTextString(cellValue != null ? cellValue.toString() : "");
cell.setCellValue(text);
int colWidth = cellValue != null ? cellValue.toString().getBytes().length * 1 * 256 : "".toString().getBytes().length * 1 * 256;// 长度乘以2是为了解决纯数字列宽度不足会显示科学计数法问题, 乘以256得到的数据才是excel真实列宽
colWidths[j] = colWidths[j] > colWidth ? colWidths[j] : colWidth;
}
++rowNumber;
//是否合并单元格
if (isMerge) {
if(!departmentname.equals(dataList.get(i).get("departmentname").toString().trim())){
rowList.add(rowNumber);
departmentname = dataList.get(i).get("departmentname").toString().trim();
++number;
}
}
}
if (isMerge) {
//合并单元格
//1.合并公司
//合并第一列
CellRangeAddress region = new CellRangeAddress(2, rowNumber-1, 1, 1);
//合并第二列
CellRangeAddress region1 = new CellRangeAddress(2, rowNumber-1, 2, 2);
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
//2.合并部门
if (number > 0) {
for (int k = 0; k < number; k++) {
for(int x = 3; x < 7; x++){
CellRangeAddress regionX = new CellRangeAddress(rowList.get(2*k), rowList.get(2*k+1), x, x);
sheet.addMergedRegion(regionX);
}
}
}
}
//设置列宽
for (int i = 0; i < colWidths.length; i++) {
// sheet.autoSizeColumn(i); // 自适应列宽, 只对英文、数字有用
//增加长度判断解决 maximum column width for an individual cell is 255 characters 异常问题
int colWidth = colWidths[i]*2;
if (colWidth < 255 * 256) {
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
} else {
sheet.setColumnWidth(i, 6000);
}
}
}
框架工具类方法:
ExcelUtil.downloadExcel();
/**
* 下载Excel文件
* @param request
* @param response
* @param filePath Excel文件路径
* @return
* @throws Exception
*/
public static int downloadExcel(HttpServletRequest request, HttpServletResponse response, String filePath) throws Exception {
// 报告下载状态: 1为成功;0为失败
int status = 0;
Exception ex1 = null;
try {
File file = new File(filePath);
response.reset();
response.setCharacterEncoding(ENCODING);
if (file.exists()) {
String fileFullName = file.getName();
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileFullName, ENCODING));
response.addHeader("Content-Type", "application/vnd.ms-excel");
InputStream is = new FileInputStream(file);
OutputStream os = response.getOutputStream();
byte[] b = new byte[1024];
int len;
while ((len = is.read(b)) > 0) {
os.write(b, 0, len);
}
os.flush();
os.close();
is.close();
} else {
response.setHeader("Content-type", "text/html;charset=" + ENCODING);
OutputStream os = response.getOutputStream();
String msg = (filePath + "文件不存在");
os.write(msg.getBytes(ENCODING));
os.flush();
os.close();
LOGGER.error(msg);
}
} catch (Exception ex) {
ex1 = ex;
status = 0;
} finally {
FileUtil.deleteFile(filePath);
}
if (ex1 != null) {
throw ex1;
}
return status;
}
/**
* 下载Excel文件
* @param filePath Excel文件路径
* @return
* @throws Exception
*/
public static int downloadExcel(String filePath) throws Exception {
return downloadExcel(ServletActionContext.getRequest(), ServletActionContext.getResponse(), filePath);
}