前面两篇文章已经介绍了POI导出表格的基本使用、常见excel的导出。
2.《实操:使用HSSFWorkbook导出一份Excel版个人信息表》
使用HSSFWorkbook导出、操作excel使用HSSFWorkbook导出一份Excel版个人信息表 这一篇文章是介绍导出一个excel创建多个sheet工作表的示例。有时候我们需要对数据归纳分类处理,因此会需要多个工作表展示,让数据更加清晰、一目了然。 下面是效果图:
一:导出表格方法
/** * 导出学校信息,有多个工作表sheet * @param request * @param response */ public void exportExcel(HttpServletRequest request,HttpServletResponse response) { try { String fileName = URLDecoder.decode(request.getParameter("fileName"),"UTF-8"); response.setContentType("application/msexcel"); response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "8859_1") + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); OutputStream os = response.getOutputStream(); Map<String,List<School>>resultMap = School.getJsonMap(); if(null == resultMap){ workbook.write(os); os.close(); return; } createExcelSheet(workbook,"文科",resultMap); createExcelSheet(workbook,"理科",resultMap); os = response.getOutputStream(); workbook.write(os); os.close(); } catch (Exception e) { e.printStackTrace(); } }
二:生成单个工作表方法
/** * 生成单个工作表 * @param workbook * @param sheetName * @param dataMap */ public static void createExcelSheet(HSSFWorkbook workbook , String sheetName, Map<String,List<School>> dataMap){ try { HSSFSheet sheet = workbook.createSheet(sheetName); int columnSize = 6; //设置列宽 for(int i = 0;i< columnSize;i++ ){ sheet.setColumnWidth(i, 4000); } sheet.setColumnWidth(2, 10000); sheet.setColumnWidth(4, 15000); // 设置字体 HSSFFont headfont = workbook.createFont(); headfont.setFontName("宋体"); // 字体大小 headfont.setFontHeightInPoints((short) 22); // 加粗 headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //头部样式 HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); // 左右居中 headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 上下居中 headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headstyle.setLocked(true); // 自动换行 headstyle.setWrapText(true); headstyle.setBorderLeft((short) 1); headstyle.setLeftBorderColor(HSSFColor.BLACK.index); headstyle.setRightBorderColor(HSSFColor.BLACK.index); headstyle.setBottomBorderColor(HSSFColor.BLACK.index); headstyle.setBorderBottom((short) 1); headstyle.setBorderRight((short) 1); // 加粗字体样式 HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName("宋体"); columnHeadFont.setFontHeightInPoints((short) 12); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 列头的样式 HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setTopBorderColor(HSSFColor.BLACK.index); columnHeadStyle.setBorderTop((short) 1); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index); columnHeadStyle.setBorderLeft((short) 1); columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index); columnHeadStyle.setBorderRight((short) 1); columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); columnHeadStyle.setBorderBottom((short) 1); HSSFFont font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12); /**内容居中单元格样式*/ HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setTopBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderTop((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderBottom((short) 1); centerstyle.setFillForegroundColor(HSSFColor.WHITE.index); /**内容居左单元格样式*/ HSSFCellStyle leftstyle = workbook.createCellStyle(); leftstyle.setFont(font); leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); leftstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); leftstyle.setWrapText(true); leftstyle.setLeftBorderColor(HSSFColor.BLACK.index); leftstyle.setBorderLeft((short) 1); leftstyle.setTopBorderColor(HSSFColor.BLACK.index); leftstyle.setBorderTop((short) 1); leftstyle.setRightBorderColor(HSSFColor.BLACK.index); leftstyle.setBorderRight((short) 1); leftstyle.setBottomBorderColor(HSSFColor.BLACK.index); leftstyle.setBorderBottom((short) 1); leftstyle.setFillForegroundColor(HSSFColor.WHITE.index); /**跨行跨列无边框线样式*/ HSSFCellStyle rowColstyle = workbook.createCellStyle(); rowColstyle.setFont(font); rowColstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); rowColstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); rowColstyle.setWrapText(true); rowColstyle.setLeftBorderColor(HSSFColor.WHITE.index); rowColstyle.setBorderLeft((short) 1); rowColstyle.setTopBorderColor(HSSFColor.WHITE.index); rowColstyle.setBorderTop((short) 1); rowColstyle.setRightBorderColor(HSSFColor.BLACK.index); rowColstyle.setBorderRight((short) 1); rowColstyle.setBottomBorderColor(HSSFColor.WHITE.index); rowColstyle.setBorderBottom((short) 1); rowColstyle.setFillForegroundColor(HSSFColor.WHITE.index); /**字体红色无边框居左*/ HSSFFont redFont = workbook.createFont(); redFont.setFontName("宋体"); redFont.setFontHeightInPoints((short) 12); redFont.setColor(HSSFColor.RED.index); HSSFCellStyle redStyle = workbook.createCellStyle(); redStyle.setFont(redFont); redStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); redStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); redStyle.setWrapText(true); redStyle.setLeftBorderColor(HSSFColor.WHITE.index); redStyle.setBorderLeft((short) 1); redStyle.setTopBorderColor(HSSFColor.WHITE.index); redStyle.setBorderTop((short) 1); redStyle.setRightBorderColor(HSSFColor.BLACK.index); redStyle.setBorderRight((short) 1); redStyle.setBottomBorderColor(HSSFColor.WHITE.index); redStyle.setBorderBottom((short) 1); redStyle.setFillForegroundColor(HSSFColor.WHITE.index); /**字体红色有边框居中*/ HSSFCellStyle redCenterStyle = workbook.createCellStyle(); redCenterStyle.setFont(redFont); redCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); redCenterStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); redCenterStyle.setWrapText(true); redCenterStyle.setLeftBorderColor(HSSFColor.BLACK.index); redCenterStyle.setBorderLeft((short) 1); redCenterStyle.setTopBorderColor(HSSFColor.BLACK.index); redCenterStyle.setBorderTop((short) 1); redCenterStyle.setRightBorderColor(HSSFColor.BLACK.index); redCenterStyle.setBorderRight((short) 1); redCenterStyle.setBottomBorderColor(HSSFColor.BLACK.index); redCenterStyle.setBorderBottom((short) 1); redCenterStyle.setFillForegroundColor(HSSFColor.WHITE.index); /**字体红色加粗有边框居中*/ HSSFFont redBoldFont = workbook.createFont(); redBoldFont.setFontName("宋体"); redBoldFont.setFontHeightInPoints((short) 12); redBoldFont.setColor(HSSFColor.RED.index); redBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle redBoldStyle = workbook.createCellStyle(); redBoldStyle.setFont(redBoldFont); redBoldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); redBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); redBoldStyle.setWrapText(true); redBoldStyle.setLeftBorderColor(HSSFColor.BLACK.index); redBoldStyle.setBorderLeft((short) 1); redBoldStyle.setTopBorderColor(HSSFColor.BLACK.index); redBoldStyle.setBorderTop((short) 1); redBoldStyle.setRightBorderColor(HSSFColor.BLACK.index); redBoldStyle.setBorderRight((short) 1); redBoldStyle.setBottomBorderColor(HSSFColor.BLACK.index); redBoldStyle.setBorderBottom((short) 1); redBoldStyle.setFillForegroundColor(HSSFColor.WHITE.index); /**创建标题*/ HSSFRow row0 = sheet.createRow(0); /**设置行高*/ row0.setHeight((short) 900); HSSFCell cell = row0.createCell(0); cell.setCellValue(new HSSFRichTextString("*******Excel标题*******")); cell.setCellStyle(headstyle); for(int i = 1;i< columnSize;i++ ){ cell = row0.createCell(i); cell.setCellStyle(headstyle); } CellRangeAddress range = new CellRangeAddress(0, 0, 0,5); sheet.addMergedRegion(range); /**创建第二行*/ HSSFRow row1 = sheet.createRow(1); cell = row1.createCell(0); cell.setCellValue(new HSSFRichTextString("说明文字:")); row1.setHeight((short) 400); cell.setCellStyle(rowColstyle); for(int i = 1;i< columnSize;i++ ){ cell = row1.createCell(i); cell.setCellStyle(rowColstyle); } range = new CellRangeAddress(1, 1, 0,5); sheet.addMergedRegion(range); /**创建第三行*/ HSSFRow row2 = sheet.createRow(2); cell = row2.createCell(0); cell.setCellValue(new HSSFRichTextString("1、说明文字。")); row2.setHeight((short) 400); cell.setCellStyle(rowColstyle); for(int i = 1;i< columnSize;i++ ){ cell = row2.createCell(i); cell.setCellStyle(rowColstyle); } range = new CellRangeAddress(2, 2, 0,5); sheet.addMergedRegion(range); /**创建第四行*/ HSSFRow row3 = sheet.createRow(3); cell = row3.createCell(0); cell.setCellValue(new HSSFRichTextString("2、特殊提醒")); row3.setHeight((short) 400); cell.setCellStyle(redStyle); for(int i = 1;i< columnSize;i++ ){ cell = row3.createCell(i); cell.setCellStyle(redStyle); } range = new CellRangeAddress(3, 3, 0,5); sheet.addMergedRegion(range); /**创建第五行*/ HSSFRow row4 = sheet.createRow(4); cell = row4.createCell(0); cell.setCellValue(new HSSFRichTextString("3、用户协议")); row4.setHeight((short) 400); cell.setCellStyle(rowColstyle); for(int i = 1;i< columnSize;i++ ){ cell = row4.createCell(i); cell.setCellStyle(rowColstyle); } range = new CellRangeAddress(4, 4, 0,5); sheet.addMergedRegion(range); /**第六行开始创建批次数据*/ int rowNo = 5; int rowFlag = 0; for(Map.Entry<String,List<School>> map:dataMap.entrySet()){ String batName = map.getKey(); HSSFRow row5 = sheet.createRow(rowNo++); cell = row5.createCell(0); cell.setCellValue(new HSSFRichTextString(batName)); row5.setHeight((short) 700); cell.setCellStyle(columnHeadStyle); for(int i = 1;i< columnSize;i++ ){ cell = row5.createCell(i); cell.setCellStyle(columnHeadStyle); } range = new CellRangeAddress(rowNo-1, rowNo-1, 0,5); sheet.addMergedRegion(range); /**志愿表信息列标题*/ HSSFRow row6 = sheet.createRow(rowNo++); row6.setHeight((short) 700); cell = row6.createCell(0); cell.setCellValue(new HSSFRichTextString("序号")); cell.setCellStyle(columnHeadStyle); cell = row6.createCell(1); cell.setCellValue(new HSSFRichTextString("学校代码")); cell.setCellStyle(columnHeadStyle); cell = row6.createCell(2); cell.setCellValue(new HSSFRichTextString("学校名称")); cell.setCellStyle(columnHeadStyle); cell = row6.createCell(3); cell.setCellValue(new HSSFRichTextString("年份")); cell.setCellStyle(columnHeadStyle); cell = row6.createCell(4); cell.setCellValue(new HSSFRichTextString("录取分数")); cell.setCellStyle(columnHeadStyle); cell = row6.createCell(5); cell.setCellValue(new HSSFRichTextString("是否确认")); cell.setCellStyle(redBoldStyle); Map<String,List<School>> scoreMap = new LinkedHashMap<>(); for(School detail:map.getValue()){ String schoolCode = detail.getCode(); List<School> scoreList = new ArrayList<>(); if(scoreMap.containsKey(schoolCode)){ scoreList = scoreMap.get(schoolCode); } scoreList.add(detail); scoreMap.put(schoolCode,scoreList); } int r = 0; for(Map.Entry<String,List<School>> s:scoreMap.entrySet()){ r++; for(School detail:s.getValue()){ /**志愿表信息列*/ HSSFRow row7 = sheet.createRow(rowNo++); row7.setHeight((short) 700); cell = row7.createCell(0); cell.setCellValue(r); cell.setCellStyle(centerstyle); cell = row7.createCell(1); cell.setCellValue(detail.getCode()); cell.setCellStyle(centerstyle); cell = row7.createCell(2); cell.setCellValue(detail.getName()); cell.setCellStyle(centerstyle); cell = row7.createCell(3); cell.setCellValue(detail.getYear()); cell.setCellStyle(centerstyle); cell = row7.createCell(4); cell.setCellValue(detail.getScore()); cell.setCellStyle(leftstyle); cell = row7.createCell(5); cell.setCellValue(1 == detail.getIs_confirm()?"是":"否"); cell.setCellStyle(redCenterStyle); } /**合并单元格*/ range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 0, 0); sheet.addMergedRegion(range); range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 1, 1); sheet.addMergedRegion(range); range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 2, 2); sheet.addMergedRegion(range); range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 5, 5); sheet.addMergedRegion(range); } /**创建空行*/ if(dataMap.size() > ++rowFlag){ HSSFRow row8 = sheet.createRow(rowNo++); cell = row8.createCell(0); row8.setHeight((short) 700); cell.setCellStyle(centerstyle); for(int i = 1;i< columnSize;i++ ){ cell = row8.createCell(i); cell.setCellStyle(centerstyle); } range = new CellRangeAddress(rowNo-1, rowNo-1, 0,5); sheet.addMergedRegion(range); } } }catch (Exception e){ e.printStackTrace(); } }
三:导出的数据在实际开发中一般是数据库查询出来的数据,这个示例是学校分数线的测试数据,因此创建了一个枚举类来存放我们的测试数据,方便我们在导出数据的时候访问对象。 School枚举类如下:
package com.qiuxuebao.app.controller.expert.bds; import java.util.ArrayList; import java.util.HashMap; import java.util.List; /** * Created by hs011 on 2019/1/26. */ public enum School { data1("01","北京大学",1,"本科一批",2016,"最低分:666",1), data2("01","北京大学",1,"本科一批",2017,"最低分:665",1), data3("01","北京大学",1,"本科一批",2018,"最低分:664",1), data4("02","清华大学",1,"本科一批",2016,"平均分:680",0), data5("02","清华大学",1,"本科一批",2017,"平均分:682",0), data6("02","清华大学",1,"本科一批",2018,"平均分:681",0), data7("03","软件学院",2,"本科二批",2016,"最高分:506",1), data8("03","软件学院",2,"本科二批",2017,"最高分:488",1), data9("03","软件学院",2,"本科二批",2018,"最高分:489",1); /**学校编码*/ private String code; /**学校名称*/ private String name; /**学校批次*/ private int bat; /**批次名称*/ private String batName; /**年份*/ private int year; /**录取分数*/ private String score; /**是否确认*/ private int is_confirm; private School(String code,String name,int bat,String batName,int year,String score,int is_confirm) { this.code = code; this.name = name; this.bat = bat; this.batName = batName; this.year = year; this.score = score; this.is_confirm = is_confirm; } /** * 获取所有的学校 * @return */ public static HashMap<String, List<School>> getJsonMap() { HashMap<String, List<School>> jsonMap = new HashMap<String, List<School>>(); for (School school : School.values()) { if(jsonMap.containsKey(school.getBatName())){ jsonMap.get(school.getBatName()).add(school); }else{ List<School> list = new ArrayList<>(); list.add(school); jsonMap.put(school.getBatName(),list); } } return jsonMap; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getBat() { return bat; } public void setBat(int bat) { this.bat = bat; } public int getYear() { return year; } public void setYear(int year) { this.year = year; } public String getScore() { return score; } public void setScore(String score) { this.score = score; } public int getIs_confirm() { return is_confirm; } public void setIs_confirm(int is_confirm) { this.is_confirm = is_confirm; } public String getBatName() { return batName; } public void setBatName(String batName) { this.batName = batName; } }
四:前端请求导出excel的方法:
function exportExcel(){ var fileName = "学校分数线"; window.location.href = encodeURI("exportExcel?fileName="+encodeURIComponent(fileName)); }
我们正在应用程序中进行更改,将文件扩展名从xls转换为xlsx格式(2007及以上),同时从应用程序中导出文件。 通过升级到POI3.9JAR,我添加了HSSF的XSSF intead,从而进行了必要的代码更改。我们使用的Jar文件的前一个版本是POI3.0。 以下是我在库文件夹中添加的JAR:
VB.Net支持Microsoft Excel 2010的COM对象模型与您的应用程序之间的互操作性。 要在应用程序中使用此互操作性,您需要在Windows窗体应用程序中导入名称空间Microsoft.Office.Interop.Excel 。 从VB.Net创建Excel应用程序 让我们首先按照Microsoft Visual Studio中的以下步骤创建一个Window窗体应用程序: Fil
我正在从两个不同的谷歌工作表导入数据,并将它们合并到另一个工作表中。我使用的公式如下。 在上面的公式中,当两个工作表都有数据要导入时,importrange给出了结果,但是如果其中一个工作表没有数据要导入,则不会从另一个工作表导入数据。我在下面的链接中找到了一个解决方案,我们可以通过使用虚拟列引用来处理这个问题,当我们用很少的列导入很少的工作表时,这种方法是可以的,但是当我们从许多工作表和许多列导
我得到了这个错误: 工作簿已包含此名称的工作表 行应该检查工作簿是否已经包含此名称的工作表,但它不工作。我仍然有错误。 谢谢你的帮助!
方法创建新文件
我面临的情况是,我必须将条件格式从一个Excel工作表复制到另一个。 这就是我尝试过的; 源和目标都是XSSF。 但是当我试图打开目标工作表时,excel显示一条消息,提示工作表中有一些错误,我要恢复它吗?如果单击“是”,工作表将打开,但条件格式不能正确应用。 修复 excel 后显示一条消息,例如; Excel能够通过修复或删除不可读的内容来打开文件 在这种情况下,我的条件格式就像如果单元格区域
我在Google Sheets中使用IMPORTRANGE将多个工作表导入主工作表上的一列时遇到了一点问题,每当其中一个工作表被更新时,它就会自动在主工作表上创建新行。除了进口,还有更好的方法吗?
我创建了一个脚本来生成一些PDF,保存它们以驱动并在需要时发送电子邮件。 该脚本工作正常,除了一个问题:当我隐藏名为“TrafficAgentPDF”的工作表并运行脚本时,它会在驱动器中创建PDF,但不知何故它已损坏。不能被谷歌打开;在浏览器中打开它时,它是空白的。打开工作表,一切正常。 TrafficAgentPDF工作表在另一个工作表上执行vlookup,以显示图像而不是值。图像是小图标,仅使