创建Excel，解析Excel，处理CSV Injection

本文主要是介绍创建Excel，解析Excel，处理CSV Injection，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1.使用Apache POI创建和解析Excel

Maven依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version>
</dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version>
</dependency>

2.本地创建Excel

3.如何处理CSV Injection

如果遇到=,+,-,@符号特殊字符打头的值，那么我会在其值前面加一个tab键，这样在excel里面tab键不会显示，但实际上有该值前面有个tab键。
下载：用户下载excel的时候，对于特殊字符打头的我们加一个tab键
上传：用户修改好excel内容，上传到系统的时候，我们需要trim一下
作用：防止CSV 注入；用户在界面上填写的内容生成excel，之后上传到界面以后展示才能够保持一致。

4.具体代码（做了trim）

public class Leo_Test
{public static void main(String[] args) throws IOException, EncryptedDocumentException, InvalidFormatException, org.apache.poi.openxml4j.exceptions.InvalidFormatException{//Blank workbookXSSFWorkbook xssfWorkbook = new XSSFWorkbook();//Create a blank sheetXSSFSheet sheet = xssfWorkbook.createSheet("test");//This data needs to be written (Object[])Map<String, Object[]> data = new TreeMap<String, Object[]>();data.put("1", new Object[]{ "ID", "NAME", "LAST NAM E" });data.put("2", new Object[]{ 1, "=calc|A!Z", "-3+2" });//Iterate over data and write to sheetSet<String> keyset = data.keySet();int rownum = 0;for (String key : keyset){Row row = sheet.createRow(rownum++);Object[] objArr = data.get(key);int cellnum = 0;for (Object obj : objArr){Cell cell = row.createCell(cellnum++);if (obj instanceof String){if(((String) obj).startsWith("=")||((String) obj).startsWith("+")||((String) obj).startsWith("-")||((String) obj).startsWith("@")){StringBuffer sb = new StringBuffer();sb.append("\t").append(obj);cell.setCellValue(sb.toString());}else{cell.setCellValue((String) obj);}}else if (obj instanceof Integer)cell.setCellValue((Integer) obj);}}try{//Write the workbook in file systemFileOutputStream out = new FileOutputStream(new File("test.xlsx"));xssfWorkbook.write(out);out.close();}catch (Exception e){e.printStackTrace();}System.out.println("I will read excel content");File file = new File("test.xlsx");readFileContent(file);}private static void readFileContent(File file) throws EncryptedDocumentException, org.apache.poi.openxml4j.exceptions.InvalidFormatException, IOException{FileInputStream fileInputStream = new FileInputStream(file);Workbook workbook = WorkbookFactory.create(fileInputStream);if (null != workbook){//to get each cell value of excelfor (int i = 0; i < workbook.getNumberOfSheets(); i++){Sheet sheet = workbook.getSheetAt(i);Iterator<Row> rowIterator = sheet.iterator();while (rowIterator.hasNext()){Row row = rowIterator.next();Iterator<Cell> cellIterator = row.iterator();while (cellIterator.hasNext()){Cell cell = cellIterator.next();DataFormatter dataFormatter = new DataFormatter();String cellValue = dataFormatter.formatCellValue(cell);System.out.println(cellValue.trim());}}}}}
}

5.结果