Poi

形容poi-tl版本v1.8.2 成果 模板 problem.docx{{?listMap}}{{no}}. {{content}}{{*pictures}}{{/listMap}} 代码实现public class PoitlTest { public static void main(String[] args) throws IOException { test(); } private static void test() throws IOException { // 创立列表策略 NumbericRenderPolicy policy = new NumbericRenderPolicy(); ConfigureBuilder builder = Configure.newBuilder(); // 为pictures指定列表策略 builder.bind("pictures", policy).build(); // 多级列表数据，因为列表中可能是Text或者Picture，所以类型采纳RenderData List<RenderData> detailList = new ArrayList<>(); List<RenderData> detailList2 = new ArrayList<>(); TextRenderData textRenderData1 = new TextRenderData(); TextRenderData textRenderData2 = new TextRenderData(); textRenderData1.setText("选项1"); textRenderData2.setText("选项2"); BufferedImage bufferedImage1 = ImageIO.read(new URL("https://dev-nfs.sf8cloud.com/receive/EZ-NFS-22a36097-47de-451a-877d-730598ba9166.png")); BufferedImage bufferedImage2 = ImageIO.read(new URL("https://dev-nfs.sf8cloud.com/receive/EZ-NFS-c8a9d8fd-9a4b-4376-a44c-00e87030aa71.gif")); PictureRenderData pictureRenderData1 = new PictureRenderData(100, 120, ".png",bufferedImage1); PictureRenderData pictureRenderData2 = new PictureRenderData(100, 120, ".png",bufferedImage2); detailList.add(textRenderData1); detailList.add(pictureRenderData1); detailList2.add(textRenderData2); detailList2.add(pictureRenderData2); // 应用NumbericRenderData包装列表数据，这样模板中能够间接应用{{*pictures}}机型渲染; NumbericRenderData numbericRenderData1 = new NumbericRenderData(Pair.of(STNumberFormat.DECIMAL, ""),detailList); NumbericRenderData numbericRenderData2 = new NumbericRenderData(Pair.of(STNumberFormat.DECIMAL, ""),detailList2); TestPoiMain main = new TestPoiMain(); List<Map<String, Object>> amapList = new ArrayList<>(); Map<String, Object> bmap = new HashMap<String, Object>(16); bmap.put("pictures", numbericRenderData1); bmap.put("no", "A"); bmap.put("content", "这是一个题目"); amapList.add(bmap); Map<String, Object> bmap2 = new HashMap<String, Object>(16); bmap2.put("pictures", numbericRenderData2); bmap2.put("no", "B"); bmap2.put("content", "这是第二个个题目"); amapList.add(bmap2); main.setListMap(amapList); XWPFTemplate template = XWPFTemplate.compile("D:\\problem.docx", builder.build()).render(main); FileOutputStream out = new FileOutputStream("D:\\output.docx"); template.write(out); }}public class TestPoiMain { List<Map<String, Object>> listMap; public List<Map<String, Object>> getListMap() { return listMap; } public void setListMap(List<Map<String, Object>> listMap) { this.listMap = listMap; }}

public void exportExcel(List<List<String>> list,HttpServletResponse response,String fileName) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheetCs = wb.createSheet("sheet1"); //为首行赋值 for(int i=0;i<list.size();i++){ XSSFRow row = sheetCs.createRow(i); for(int j=0;j<list.get(i).size();j++){ sheetCs.setColumnWidth(j, 3500);//设置列宽 XSSFCell cell = row.createCell(j); cell.setCellValue(list.get(i).get(j)); } } int sheetTotal=wb.getNumberOfSheets(); XSSFRow row; //第一个下拉选 //新建一个sheet页 String hiddenSheetName = "hiddenSheet" + sheetTotal; XSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName); //数据 String[] selectList =new String[100]; //对应列下拉框数据 for(int i=0;i<100;i++){ selectList[i]="测试数据"+i; } //写入下拉数据到新的sheet页中 for (int i = 0; i < selectList.length; i++) { row = hiddenSheet.createRow(i); XSSFCell cell = row.createCell(0); cell.setCellValue(selectList[i]); } //获取新sheet页内容 String strFormula = hiddenSheetName + "!$A$1:$A$65535"; XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula); // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(1,65535, 0, 0); // 数据有效性对象 DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheetCs); DataValidation validation = help.createValidation(constraint, regions); sheetCs.addValidationData(validation); //将新建的sheet页隐藏掉 wb.setSheetHidden(sheetTotal, true); sheetTotal++; //第二个下拉选 hiddenSheetName = "hiddenSheet" + sheetTotal; XSSFSheet hiddenSheet2 = wb.createSheet(hiddenSheetName); String[] selectList2 =new String[100]; //对应列下拉框数据 for(int i=0;i<100;i++){ selectList2[i]="测试数据2"+i; } for (int i = 0; i < selectList2.length; i++) { row = hiddenSheet2.createRow(i); XSSFCell cell = row.createCell(0); cell.setCellValue(selectList2[i]); } String strFormula2 = hiddenSheetName + "!$A$1:$A$65535"; XSSFDataValidationConstraint constraint2 = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula2); CellRangeAddressList regions2 = new CellRangeAddressList(1,65535, 1, 1); DataValidationHelper help2 = new XSSFDataValidationHelper((XSSFSheet) sheetCs); DataValidation validation2 = help2.createValidation(constraint2, regions2); sheetCs.addValidationData(validation2); wb.setSheetHidden(sheetTotal, true); sheetTotal++; try { OutputStream output = response.getOutputStream(); fileName = URLEncoder.encode(fileName,"UTF-8"); response.addHeader("Content-Disposition", "inline;filename="+fileName+".xls"); response.setContentType("application/msexcel"); wb.write(output); output.flush(); output.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } // 结束 System.out.println("Over");} ...

问题描述现需要批量导入数据，数据以Excel形式导入。POI介绍我选择使用的是apache POI。这是有Apache软件基金会开放的函数库，他会提供API给java，使其可以对office文件进行读写。我这里只需要使用其中的Excel部分。实现首先，Excel有两种格式，一种是.xls(03版),另一种是.xlsx(07版)。针对两种不同的表格格式，POI对应提供了两种接口。HSSFWorkbook和XSSFWorkbook导入依赖<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>RELEASE</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>RELEASE</version></dependency>处理版本Workbook workbook = null;try { if (file.getPath().endsWith(“xls”)) { System.out.println(“这是2003版本”); workbook = new XSSFWorkbook(new FileInputStream(file)); } else if (file.getPath().endsWith(“xlsx”)){ workbook = new HSSFWorkbook(new FileInputStream(file)); System.out.println(“这是2007版本”); } } catch (IOException e) { e.printStackTrace();}这里需要判断一下Excel的版本，根据扩展名，用不同的类来处理文件。获取表格数据获取表格中的数据分为以下几步：1.获取表格2.获取某一行3.获取这一行中的某个单元格代码实现：// 获取第一个张表Sheet sheet = workbook.getSheetAt(0); // 获取每行中的字段for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); // 获取行 // 获取单元格中的值 String studentNum = row.getCell(0).getStringCellValue(); String name = row.getCell(1).getStringCellValue(); String phone = row.getCell(2).getStringCellValue();}持久化获取出单元格中的数据后，最后就是用数据建立对象了。List<Student> studentList = new ArrayList<>();for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); // 获取行 // 获取单元格中的值 String studentNum = row.getCell(0).getStringCellValue(); String name = row.getCell(1).getStringCellValue(); String phone = row.getCell(2).getStringCellValue(); Student student = new Student(); student.setStudentNumber(studentNum); student.setName(name); student.setPhoneNumber(phone); studentList.add(student);}// 持久化studentRepository.saveAll(studentList);相关参考：https://poi.apache.org/compon…https://blog.csdn.net/daihuim… ...