使用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();
}
}
}
}
京公网安备 11010502036488号