1,需要依赖的jar包,
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
org.apache.poi
poi-ooxml-schemas
3.17
org.apache.xmlbeans
xmlbeans
2.6.0
commons-collections
commons-collections
3.2.2
dom4j
dom4j
1.6.1
JAR packages that need to be dependent
2,创建Excel(.xls 和 .xlsx的方法)我详细地写了如何创建.xls 文件,创建.xlsx文件就是引用的类不一样,步骤基本上是一样的。
/**
*
*/
package com.nokia.jira.utils;
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 bpan
*
* created 2018年2月27日
*/
public class CreateExcelFile {
private static HSSFWorkbook hWorkbook = null;
private static XSSFWorkbook xWorkbook = null;
/**
* 判断文件是否存在.
* @param fileDir 文件路径
* @return
*/
public static boolean fileExist(String fileDir){
boolean flag = false;
File file = new File(fileDir);
flag = file.exists();
return flag;
}
/**
* 判断文件的sheet是否存在.
* @param fileDir 文件路径
* @param sheetName 表格索引名
* @return boolean
*/
public static boolean XlsSheetExist(String fileDir, String sheetName){
boolean flag = false;
File file = new File(fileDir);
if (file.exists()) {
//文件存在,创建workbook
try {
hWorkbook = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = hWorkbook.getSheet(sheetName);
if (sheet!=null) {
//文件存在,sheet存在
flag = true;
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else {
//文件不存在
flag = false;
}
return flag;
}
/**
* 创建新excel(xls).
* @param fileDir excel的路径
* @param sheetNames 要创建的表格索引列表
* @param titleRow excel的第一行即表格头
*/
public static void createExcelXls(String fileDir, List sheetNames, String titleRow[]){
//创建workbook
hWorkbook = new HSSFWorkbook();
//新建文件
FileOutputStream fileOutputStream = null;
HSSFRow row = null;
try {
CellStyle cellStyle = hWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
for(int i = 0; i |