POI⽣成Excel
引⼊jar包
⾸先，在maven中l添加
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
白鹇<version>3.17</version>
</dependency>
要⽤XSSFWorkbook和SXSSHWorkbook（处理⼤量数据⽤）还需引⼊
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
代码
新建Excel⽂件，并制定Excel版本，表单名称，表头，数据及Excel⽂件路径
/**
* 新建Excel⽂件，New Workbook
* @param excelType 可为null，Excel版本，可为2003（.xls）或者2007（.xlsx），默认为2003版本
* @param sheetName 新建表单名称
* @param headList 表头List集合
* @param dataList 数据List<List<；集合>>(⾏<；列>)
* @param path 新建excel路径
* @return
*/
public static boolean createWorkBook(String excelType, String sheetName, List<String> headList,List<List<String>> dataList,String path){
Workbook wb = null;
/*创建⽂件*/
if (excelType == null || dsWith("2003")) {
/*操作Excel2003以前（包括2003）的版本，扩展名是.xls */
wb = new HSSFWorkbook();
}el if (dsWith("2007")){
/*XSSFWorkbook：是操作Excel2007的版本，扩展名是.xlsx */
wb = new XSSFWorkbook();
}el { //默认为2003版本
/*操作Excel2003以前（包括2003）的版本，扩展名是.xls */
wb = new HSSFWorkbook();
}
/*Excel⽂件创建完毕*/
CreationHelper createHelper = wb.getCreationHelper(); //创建帮助⼯具
/*创建表单*/
Sheet sheet = wb.createSheet(sheetName!=null?sheetName:"new sheet");
// Note that sheet name is Excel must not exceed 31 characters（注意sheet的名字的长度不能超过31个字符，若是超过的话，会⾃动截取前31个字符） // and must not contain any of the any of the following characters:（不能包含下列字符）
// 0x0000 0x0003 colon (:) backslash (\) asterisk (*) question mark (?) forward slash (/) opening square bracket ([) closing square bracket (]) /*若是包含的话，会报错。但有⼀个解决此问题的⽅法，
就是调⽤WorkbookUtil的createSafeSheetName(String nameProposal)⽅法来创建sheet name,
若是有如上特殊字符，它会⾃动⽤空字符来替换掉，⾃动过滤。*/
/*String safeName = ateSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "过滤掉上⾯出现的不合法字符
Sheet sheet3 = ateSheet(safeName); //然后就创建成功了*/
/*表单创建完毕*/
//设置字体
Font headFont = wb.createFont();
headFont.tFontHeightInPoints((short)14);置若罔闻的意思
headFont.tFontName("Courier New");
headFont.tItalic(fal);
headFont.tStrikeout(fal);
//设置头部单元格样式
CellStyle headStyle = wb.createCellStyle();
headStyle.tBorderBottom(BorderStyle.THICK); //设置单元格线条
headStyle.tBottomBorderColor(Index()); //设置单元格颜⾊
headStyle.tBorderLeft(BorderStyle.THICK);
headStyle.tLeftBorderColor(Index());
headStyle.tBorderRight(BorderStyle.THICK);
headStyle.tRightBorderColor(Index());
headStyle.tBorderTop(BorderStyle.THICK);
headStyle.tTopBorderColor(Index());
headStyle.tAlignment(HorizontalAlignment.CENTER); //设置⽔平对齐⽅式
headStyle.tVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐⽅式
//headStyle.tShrinkToFit(true); //⾃动伸缩
headStyle.tFont(headFont); //设置字体
/*设置数据单元格格式*/
CellStyle dataStyle = wb.createCellStyle();
dataStyle.tBorderBottom(BorderStyle.THIN); //设置单元格线条
dataStyle.tBottomBorderColor(Index()); //设置单元格颜⾊
dataStyle.tBorderLeft(BorderStyle.THIN);
dataStyle.tLeftBorderColor(Index());
dataStyle.tBorderRight(BorderStyle.THIN);
dataStyle.tRightBorderColor(Index());
dataStyle.tBorderTop(BorderStyle.THIN);
dataStyle.tTopBorderColor(Index());
dataStyle.tAlignment(HorizontalAlignment.LEFT); //设置⽔平对齐⽅式
dataStyle.tVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐⽅式
//dataStyle.tShrinkToFit(true); //⾃动伸缩
/*创建⾏Rows及单元格Cells*/
Row headRow = ateRow(0); //第⼀⾏为头
for (int i=0;i<headList.size();i++){ //遍历表头数据
生物制氢
Cell cell = ateCell(i); //创建单元格
cell.(i))); //设置值
cell.tCellStyle(headStyle); //设置样式
}
int rowIndex = 1; //当前⾏索引
//创建Rows
for (List<String> rowdata : dataList){ //遍历所有数据
Row row = ateRow(rowIndex++); //第⼀⾏为头
for (int j = 0;j< rowdata.size();j++){ //编译每⼀⾏
Cell cell = ateCell(j);
cell.tCellStyle(dataStyle);
cell.(j)));
}
}
/*创建rows和cells完毕*/
/*设置列⾃动对齐*/
for (int i =0;i<headList.size();i++){
sheet.autoSizeColumn(i);
}
try (OutputStream fileOut = new FileOutputStream(path)) { //获取⽂件流
wb.write(fileOut); //将workbook写⼊⽂件流
} catch (FileNotFoundException e) {
e.printStackTrace();
return fal;
} catch (IOException e) {
e.printStackTrace();
return fal;
}
return true;
}
⼯具类其他⽅法
public static Workbook getWorkBook(String path) throws IOException, InvalidFormatException { File file = new File(path); //⽬标Excel⽂件
File parent = new Parent()); //Excel的⽗⽂件夹
if (!ists()){ //判断⽗⽂件夹是否存在
parent.mkdirs(); //建⽴⽗⽂件夹
玫瑰花代表什么
}
Workbook wb = null;
ists()){ //如果⽂件存在毕业晚会
wb = ate(file); //打开⽂件
}el { //如果⽬标⽂件不存在
if (dsWith(".xls")) {
/*操作Excel2003以前（包括2003）的版本，扩展名是.xls */
wb = new HSSFWorkbook();
} el if (dsWith(".xlsx")) {
/
*XSSFWorkbook：是操作Excel2007的版本，扩展名是.xlsx */
wb = new XSSFWorkbook();
} el {
new Exception(path + "后缀名错误!");
}
}
/*获取表单总数*/
int sheetCount = wb.getNumberOfSheets();
灭绝的意思
/*获取某⼀表单总⾏数*/
Sheet sheet = wb.createSheet();
//Sheet sheet = wb.getSheetAt(0);
System.out.println("表单数:"+sheetCount);
System.out.println("第⼀⾏索引:"+FirstRowNum());
System.out.println("最后⼀⾏索引"+LastRowNum());
int rowIndex = LastRowNum()+1;
for (int i=0;i<=3;i++) {
Row row = ateRow(rowIndex++);
for (int j = 0; j < 10; j++) {
Cell cell = ateCell(j);
cell.tCellValue("格"+i + j);
}
}
try (OutputStream fileOut = new FileOutputStream(file)) { //获取⽂件流
fileOut.flush();
wb.write(fileOut); //将workbook写⼊⽂件流
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}爬上豆蔓看自己
return wb;
}
//创建⼀个不存在的excel⽂件
private static Workbook createNewWorkbookIfNotExist(String fileName) throws Exception {
Workbook wb = null;
dsWith(".xls")) {
wb = new HSSFWorkbook();
} el dsWith(".xlsx")) {
wb = new XSSFWorkbook();
} el {
throw new Exception("⽂件类型错误！既不是.xls也不是.xlsx");
}
try{
OutputStream output = new FileOutputStream(fileName);
wb.write(output);
}catch(FileNotFoundException e) {
System.out.println("⽂件创建失败，失败原因为：" + e.getMessage());
throw new FileNotFoundException();
}
System.out.println(fileName + "⽂件创建成功！");
return wb;
}
//创建⼀个新的或者已存在的Excel⽂档的Workbook
public static Workbook createWorkbook(String fileName) throws Exception {
InputStream input = null;
Workbook wb = null;
try{
input = new FileInputStream(fileName);
wb = ate(input);
if (!new File(fileName).exists()){ //如果不存在
wb = createNewWorkbookIfNotExist(fileName); //创建新的
}
} catch(OldExcelFormatException e) {
System.out.println("⽂件打开失败，原因：要打开的Excel⽂件版本过低！");
throw new OldExcelFormatException("⽂件版本过低");
} finally {
if(input != null) {
input.clo();
}
}
return wb;
}
//创建sheet
public static Sheet createSheet(Workbook wb , String sheetName) {
Sheet sheet = wb.getSheet(sheetName);
if(sheet == null) {
System.out.println("表单" + sheetName + "不存在，试图创建该sheet，请稍后……");
sheet = wb.createSheet(sheetName);
System.out.println("名为" + sheetName +"的sheet创建成功！");
}
return sheet;
}
//创建⾏row
public static Row createRow(Sheet sheet , int rowNum) {
Row row = Row(rowNum);
if(row == null) {
System.out.println("⾏号为：" + rowNum + "的⾏不存在，正试图创建该⾏，请稍后……"); row = ateRow(rowNum);
System.out.println("⾏号为：" + rowNum + "的⾏创建成功！");
}
return row;
}
//创建单元格cell
public static Cell createCell(Row row , int cellNum) {
Cell cell = Cell(cellNum);
if(cell == null) {
System.out.println("该单元格不存在，正在试图创建该单元格，请稍后……");
cell = ateCell(cellNum);
System.out.println("该单元格创建成功！");
}
return cell;
}
/**
* 追加到已有excel
* @param dataList 数据
* @param name ⽂件名
*/
public static void addExcel(List<LinkedHashMap<String,Object>> dataList, String name) throws IOException {
FileInputStream fileInputStream=new FileInputStream("d://"+name+".xls"); //获取d://test.xls，建⽴数据的输⼊通道
POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fileInputStream); //使⽤POI提供的⽅法得
到excel的信息
HSSFWorkbook Workbook=new HSSFWorkbook(poifsFileSystem);//得到⽂档对象
HSSFSheet Sheet(name); //根据name获取sheet表
HSSFCellStyle cellStyle = ateCellStyle();
/*cellStyle.tBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.tBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.tBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.tBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
鸡的英文cellStyle.tAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中*/
// HSSFRow Row(0); //获取第⼀⾏
System.out.println("最后⼀⾏的⾏号 :"+LastRowNum() + 1); //分别得到最后⼀⾏的⾏号，和
第3条记录的最后⼀个单元格 //System.out.println("最后⼀个单元格 :"+LastCellNum()); //分别得到最后⼀⾏的⾏号，和第3条记录的最后⼀个单元格
// HSSFRow ateRow((short)(LastRowNum()+1)); // 追加开始⾏
// -----------------追加数据-------------------
int start = LastRowNum() + 1; //插⼊数据开始⾏
for (int i = 0; i < dataList.size(); i++){
HSSFRow startRow = ateRow(i+start);
AtomicInteger j = new AtomicInteger();
LinkedHashMap<String ,Object> ltem = (i);
}
// 输出Excel⽂件
FileOutputStream out=new FileOutputStream("d://"+name+".xls"); //向d://test.xls中写数据
out.flush();
Workbook.write(out);
out.clo();
}
public static void readExcel(String name) throws IOException {
FileInputStream fileInputStream=new FileInputStream("d:\\"+name+".xls"); //获取d://test.xls，建⽴数据的输⼊通道
POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fileInputStream); //使⽤POI提供的⽅法得到excel的信息
HSSFWorkbook Workbook=new HSSFWorkbook(poifsFileSystem);//得到⽂档对象
HSSFSheet Sheet(name); //根据name获取sheet表
HSSFRow Row(1); //获取第⼆⾏（第⼀⾏⼀般是标题）
int lastRow = LastRowNum(); // 返回的是值从0开始的
System.out.println("总⾏数：" + (lastRow + 1));
int lastCell = LastCellNum(); // 返回的值是从1开始的.....
System.out.println("总列数：" + lastCell);
for (int i = 0; i <= lastRow; i++){
Row(i);
if (row != null){
for (int j = 0; j < lastCell; j++){
HSSFCell cell = Cell(j);
if (cell != null) System.out.StringCellValue());
}
}
}
}
