嘀嘀嘀！让你不在掉头发
相信好多小伙伴都遇到导出excel的时候，要合并单元个
那么它来了 它来了，直接都可以使用
平行丝滑导出，难道不香吗？
话不多说，直接怼
import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import .URLEncoder;import java.text.DecimalFormat;import java.util.*;import java.util.regex.Pattern;import com.alibaba.fastjson.JSON;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;/*** @Author: Y* @Date: /5/17 9:34*/public class ExportExcel {/*** 创建excel文件** @param objData 数据* @param fileName 文件名* @param sheetName sheet名* @param columns 表头* @param mergeIndex 需要合并的列号集合* @param request * @param response* @return*/public static int exportToExcelForXlsx(List<List<Object>> objData, String fileName, String sheetName, List<String> columns, List<Integer> mergeIndex, boolean isTree, HttpServletRequest request, HttpServletResponse response) {int flag = 0;Collections.sort(mergeIndex);//将列号排序// 创建工作薄XSSFWorkbook wb = new XSSFWorkbook();// sheet1XSSFSheet sheet1 = wb.createSheet(sheetName);//设置样式XSSFCellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);//水平对齐//表头sheet1.createFreezePane(0, 1);//冻结表头XSSFRow sheet1row1 = sheet1.createRow((short) 0);sheet1row1.setHeight((short) 480);//写入表头if (columns != null && columns.size() > 0) {for (int i = 0; i < columns.size(); i++) {String column = columns.get(i);//列XSSFCell cell = sheet1row1.createCell(i);cell.setCellValue(column);}}int dataSatrtIndex = 1;//数据开始行boolean isMerge = false;if (mergeIndex != null && mergeIndex.size() != 0) {isMerge = true;}//写入数据if (objData != null && objData.size() > 0) {Map<Integer, MergeModel> poiModels = new HashMap<Integer, MergeModel>();//循环写入表中数据int i = 0;for (; i < objData.size(); i++) {//数据行XSSFRow row = sheet1.createRow((short) (i + dataSatrtIndex));//行内循环，既单元格（列）List<Object> list = objData.get(i);DecimalFormat decimalFormat = new DecimalFormat("0.00");int j = 0;for (Object o : list) {//数据列String content = "";if (o != null) {if (o.toString().contains(".") && isNumeric(o.toString())) {content = decimalFormat.format(Float.valueOf(o.toString()));} else if (o.toString().contains("-") && o.toString().contains(":")) {content = String.valueOf(o).split("\\.")[0];} else {content = String.valueOf(o);}}if (isMerge && mergeIndex.contains(j)) {//如果该列需要合并MergeModel poiModel = poiModels.get(j);if (poiModel == null) {poiModel = new MergeModel();poiModel.setContent(content);poiModel.setRowIndex(i + dataSatrtIndex);poiModel.setCellIndex(j);poiModels.put(j, poiModel);} else {if (!poiModel.getContent().equals(content)) {//如果不同了，则将前面的数据合并写入if (isTree) {//此列向后的所有列都进行一次写入合并操作，并清空。//树结构中存在这种情况，a目录和b目录为同级目录，a目录下最后一个子目录和b目录下的第一个子目录名称相同，防止本来不应该合并的单元格被合并addMergedRegionValue(sheet1, poiModels, mergeIndex, i + dataSatrtIndex, poiModel.getCellIndex());} else {XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列lastCell.setCellValue(poiModel.getContent());//合并单元格if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));}}//将新数据存入poiModel.setContent(content);poiModel.setRowIndex(i + dataSatrtIndex);poiModel.setCellIndex(j);poiModels.put(j, poiModel);}}row.createCell(j);//创建单元格} else {//该列不需要合并//数据列XSSFCell cell = row.createCell(j);cell.setCellValue(content);}j++;}}//将最后一份存入if (poiModels != null && poiModels.size() != 0) {for (Integer key : poiModels.keySet()) {MergeModel poiModel = poiModels.get(key);XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());XSSFCell lastCell = lastRow.getCell(poiModel.getCellIndex());lastCell.setCellValue(poiModel.getContent());//合并单元格if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));}}}} else {flag = -1;}// 文件在浏览器中下载//设置固定列宽，poi的列宽设置有点操蛋，大概规律网上有不少版本自行百度//这里大概是143像素for (int i = 0; i < columns.size(); i++) {sheet1.setColumnWidth(i, 4550);}OutputStream os = null;try {// 创建一个普通输出流os = response.getOutputStream();fileName = "file.xls";// 请求浏览器打开下载窗口response.reset();response.setCharacterEncoding("UTF-8");// Content-disposition 告诉浏览器以下载的形式打开// String header = request.getHeader("User-Agent").toUpperCase();// if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {//fileName = URLEncoder.encode(fileName, "utf-8");//fileName = fileName.replace("+", "%20"); // IE下载文件名空格变+号问题// } else {//fileName = new String(fileName.getBytes(), "ISO8859-1");// }fileName = new String(fileName.getBytes(), "ISO8859-1");response.setHeader("Content-Disposition", "attachment; filename=" + fileName);// 要保存的文件名response.setContentType("application/octet-stream");// 直接用数组缓冲输出流输出wb.write(os);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {wb.close();os.close();} catch (IOException e) {e.printStackTrace();}}// 文件形式输出到磁盘中// FileOutputStream out = null;// try {// String uuid = UUID.randomUUID().toString();// System.out.println(uuid);// out = new FileOutputStream("D:\\uploadPath\\download\\"+uuid+".xls"); out = new FileOutputStream("E:\\" + fileName + ".xlsx");// wb.write(out);// } catch (Exception ex) {// try {//out.flush();//out.close();// } catch (IOException e) {//flag = 0;//e.printStackTrace();// }// }return flag;}/*** 判断是不是数字** @param str* @return*/private static boolean isNumeric(String str) {if (str == null || str.length() == 0) {return false;}Pattern pattern = pile("^[-\\+]?[\\d]*$");return pattern.matcher(str).matches();}private static void addMergedRegionValue(XSSFSheet sheet, Map<Integer, MergeModel> poiModels, List<Integer> mergeIndex, int nowRowIndex, int nowCellIndex) {if (poiModels != null && poiModels.size() != 0 && mergeIndex != null && mergeIndex.size() != 0) {for (Integer index : mergeIndex) {if (index >= nowCellIndex) {MergeModel poiModel = poiModels.remove(index);//删除并获取valueif (poiModel != null) {XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex());XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列lastCell.setCellValue(poiModel.getContent());//合并单元格if (poiModel.getRowIndex() != nowRowIndex - 1) {sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), nowRowIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));}}}}}}public static void main(String[] args) {List<String> columns = new ArrayList<>();//标头columns.add("目录");columns.add("目录");columns.add("目录");columns.add("内容");String fileName = "文件名字";//文件名字String sheetName = "sheet名字";//sheet名字//内容数据List<List<Object>> exportData = new ArrayList<>();//行内的数据List<Object> rowData = new ArrayList<Object>();rowData.add("一级目录1");rowData.add("二级目录1");rowData.add("三级目录1");rowData.add("内容1");exportData.add(rowData);List<Object> rowData2 = new ArrayList<Object>();rowData2.add("一级目录1");rowData2.add("二级目录1");rowData2.add("三级目录1");rowData2.add("内容2");exportData.add(rowData2);List<Object> rowData3 = new ArrayList<Object>();rowData3.add("一级目录1");rowData3.add("二级目录1");rowData3.add("三级目录2");rowData3.add("内容3");exportData.add(rowData3);List<Object> rowData4 = new ArrayList<Object>();rowData4.add("一级目录1");rowData4.add("二级目录1");rowData4.add("三级目录2");rowData4.add("内容4");exportData.add(rowData4);List<Object> rowData5 = new ArrayList<Object>();rowData5.add("一级目录1");rowData5.add("二级目录2");rowData5.add("三级目录3");rowData5.add("内容5");exportData.add(rowData5);List<Object> rowData6 = new ArrayList<Object>();rowData6.add("一级目录1");rowData6.add("二级目录2");rowData6.add("三级目录3");rowData6.add("内容6");exportData.add(rowData6);String s = JSON.toJSONString(exportData);System.out.println(s);//需要合并的列号List<Integer> mergeIndex = new ArrayList<Integer>();mergeIndex.add(0);mergeIndex.add(1);// mergeIndex.add(2);// int flag = ExportExcel.exportToExcelForXlsx(exportData, fileName, sheetName, columns, mergeIndex, true, request, response);// System.out.println(flag);}}
调用层代码
估计但凡有点经验的都能看懂，不做任何解释了
导出结果
原文链接 /wdk/p/13204162.html
