使用Apache POI 提供的HSSFWorkbook类实现对excel的操作
pom依赖
<dependencies> <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> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --> <!-- the dependent jar package to create .xlsx file --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans --> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-collections/commons-collections --> <dependency> <groupId>commons-collections</groupId> <artifactId>commons-collections</artifactId> <version>3.2.2</version> </dependency> <!-- https://mvnrepository.com/artifact/dom4j/dom4j --> <dependency> <groupId>dom4j</groupId> <artifactId>dom4j</artifactId> <version>1.6.1</version> </dependency> </dependencies>
代码
创建了三个表(w,e,r)
//https://blog.csdn.net/nalnait/article/details/86751119 //使用Apache POI 提供的HSSFWorkbook类实现对excel的操作 package com.suixingpay; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Color; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * @author wcy * @2019年11月5日 */ public class CreateExcelFile { private static HSSFWorkbook hWorkbook = null; /** * 创建新excel(xls). * @param fileDir excel的路径 * @param sheetNames 要创建的表格索引列表 * @param titleRow excel的第一行即表格头 */ public static void createExcelXls(String fileDir, List<String> sheetNames, String titleRow[]){ //创建workbook hWorkbook = new HSSFWorkbook(); //新建文件 FileOutputStream fileOutputStream = null; HSSFRow row = null; try { //添加Worksheet(不添加sheet时生成的xls文件打开时会报错) for(int i = 0; i<sheetNames.size(); i++){ hWorkbook.createSheet(sheetNames.get(i)); hWorkbook.getSheet(sheetNames.get(i)).createRow(0); //添加表头, 创建第一行 row = hWorkbook.getSheet(sheetNames.get(i)).createRow(0); for (short j = 0; j < titleRow.length; j++) { HSSFCell cell = row.createCell(j, CellType.BLANK); cell.setCellValue(titleRow[j]); } fileOutputStream = new FileOutputStream(fileDir); hWorkbook.write(fileOutputStream); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if (fileOutputStream != null) { try { fileOutputStream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } /** * 往excel(xls)中写入(已存在的数据无法写入). * @param fileDir 文件路径 * @param sheetName 表格索引 * @param mapList * @throws Exception */ public static void writeToExcelXls(String fileDir, String sheetName, List<Map<String,String>> mapList) throws Exception{ //创建workbook File file = new File(fileDir); try { hWorkbook = new HSSFWorkbook(new FileInputStream(file)); }catch(FileNotFoundException e){ e.printStackTrace(); }catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } //文件流 FileOutputStream fileOutputStream = null; HSSFSheet sheet = hWorkbook.getSheet(sheetName); // 获取表格的总行数 // int rowCount = sheet.getLastRowNum() + 1; // 需要加一 //获取表头的列数 int columnCount = sheet.getRow(0).getLastCellNum(); try { // 获得表头行对象 HSSFRow titleRow = sheet.getRow(0); if(titleRow!=null){ for(int rowId = 0; rowId < mapList.size(); rowId++){ Map<String,String> map = mapList.get(rowId); HSSFRow newRow=sheet.createRow(rowId+1); for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) { //遍历表头 //trim()的方法是删除字符串中首尾的空格 String mapKey = titleRow.getCell(columnIndex).toString().trim(); HSSFCell cell = newRow.createCell(columnIndex); cell.setCellValue(map.get(mapKey)==null ? null : map.get(mapKey).toString()); } } } fileOutputStream = new FileOutputStream(fileDir); hWorkbook.write(fileOutputStream); } catch (Exception e) { throw e; } finally { try { if (fileOutputStream != null) { fileOutputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } } public static void main(String[] args) { //创建的文件地址 String fileDir = "d:\\测试Excel.xls"; //工作表sheet List<String> sheetName = new ArrayList<String>(); sheetName.add("w"); sheetName.add("e"); sheetName.add("r"); System.out.println(sheetName); //表头 String[] title = {"id","name","password"}; createExcelXls(fileDir, sheetName, title); //分别编辑w,e,r三张工作表的内容 List<Map<String,String>> userList1 = new ArrayList<Map<String,String>>(); Map<String,String> map=new HashMap<String,String>(); map.put("id", "1"); map.put("name", "张三"); map.put("password", "11111"); Map<String,String> map2=new HashMap<String,String>(); map2.put("id", "2"); map2.put("name", "李四"); map2.put("password", "22222"); Map<String,String> map3=new HashMap<String,String>(); map3.put("id", "3"); map3.put("name", "王五"); map3.put("password", "33333"); userList1.add(map); userList1.add(map2); userList1.add(map3); Map<String, List<Map<String, String>>> users = new HashMap<String, List<Map<String, String>>>(); users.put("w", userList1); List<Map<String,String>> userList2 = new ArrayList<Map<String,String>>(); Map<String,String> map4=new HashMap<String,String>(); map4.put("id", "111"); map4.put("name", "张三"); map4.put("password", "111!@#"); Map<String,String> map5=new HashMap<String,String>(); map5.put("id", "222"); map5.put("name", "李四"); map5.put("password", "222!@#"); Map<String,String> map6=new HashMap<String,String>(); map6.put("id", "33"); map6.put("name", "王五"); map6.put("password", "333!@#"); userList2.add(map4); userList2.add(map5); userList2.add(map6); users.put("e", userList2); List<Map<String,String>> userList3 = new ArrayList<Map<String,String>>(); Map<String,String> map7=new HashMap<String,String>(); map7.put("id", "111"); map7.put("name", "张三"); map7.put("password", "111!@#"); userList3.add(map7); users.put("r", userList3); //输出工作表页数 System.out.println(sheetName.size()); //删除内容为空的工作表 for(Iterator<String> sheeNameIterator = sheetName.iterator();sheeNameIterator.hasNext();){ String sheet = sheeNameIterator.next(); if ( users.get(sheet).size() == 0) { sheeNameIterator.remove(); } } //输出工作表页数 System.out.println(sheetName.size()); //将编辑的信息写入excel for (int j = 0; j < sheetName.size(); j++) { try { writeToExcelXls(fileDir, sheetName.get(j), users.get(sheetName.get(j))); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }