昨天组长让我将原先是导出文件格式为csv改为excel格式(也就是xls、xlsx等),在网上找了不少excel工具类,终于找到一种能用的,特意记下来(特此,该工具类不是本人原创,只是本人为了更好学习所做笔记。)
代码如下:
package cn.recour.wiicop.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.codehaus.jackson.map.ser.ArraySerializers.BooleanArraySerializer;
import org.springframework.context.annotation.EnableLoadTimeWeaving;
//import com.google.common.collect.Lists;
/**
* Excel工具类
*/
public class Excelutil {
/*
* 通用的Excel文件创建方法
* path:保存路径: C:/xls/报表名称.xls
* title:首行标题: 2015年度统计报表
* sheets:sheet的tab标签页说明: 15年度报表
* headers:表头:List存放表头 编号、姓名、备注
* datas:数据行:list存放实体数据,map存放具体每一行数据,和headers对应。
*/
public static void ExpExs(String path,String title,String sheets,Object[] headers,List<List<Object>> datas){
try {
if(sheets== null || "".equals(sheets)){ sheets = "sheet"; }
//判断文件夹是否存在,不存在则创建一个
boolean isExit0=new File(path).exists();
if(!isExit0){
File folder=new File(path);
folder.setExecutable(true);
folder.setReadable(true);
folder.setWritable(true);
folder.mkdirs();
}
String fpath=path+sheets;
boolean isExist = new File(fpath).exists();
if(!isExist){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheets);
FileOutputStream out = new FileOutputStream(new File(fpath));
workbook.write(out);
out.flush();
out.close();
}
FileInputStream file = new FileInputStream(new File(fpath));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = null;
sheet = workbook.getSheetAt(0);
if(workbook.getSheet(sheets) == null){
sheet = workbook.createSheet(sheets); //+workbook.getNumberOfSheets()
}
// if(!isExist){
// sheet = workbook.getSheetAt(0);
// }else{
// if(workbook.getSheet(sheets) == null){
// sheet = workbook.createSheet(sheets); //+workbook.getNumberOfSheets()
// }
// else{
// System.out.println("文件:["+fpath+"] ["+sheets+"] 已经存在...");
// System.out.println("");
// return;
// }
// }
HSSFRow row;
HSSFCell cell;
// 设置这些样式
HSSFFont font = workbook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);//字体
font.setFontHeightInPoints((short) 16);//字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
//font.setColor(HSSFColor.BLUE.index);//颜色
HSSFCellStyle cellStyle= workbook.createCellStyle(); //设置单元格样式
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER );
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setFont(font);
//产生表格标题行
row = sheet.createRow(0);
row.setHeightInPoints(20);
for (int i = 0; i < headers.length; i++) {
HSSFRichTextString text = new HSSFRichTextString(headers[i].toString());
cell = row.createCell(i);
cell.setCellValue(text);
cell.setCellStyle(cellStyle);
}
cellStyle= workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setDataFormat((short)0x31);//设置显示格式,避免点击后变成科学计数法了
//cellStyle.setWrapText(true);//设置自动换行
List<Object> map;
//遍历集合数据,产生数据行
for (int i=0; i <datas.size(); i++) {
row=sheet.createRow((i+1));
row.setHeightInPoints(20);
map = datas.get(i);
for(int j=0;j<map.size();j++) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if(map.get(j) != null) {
cell.setCellValue(new HSSFRichTextString(map.get(j).toString()));
}else{
cell.setCellValue(new HSSFRichTextString(""));
}
}
}
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn((short)i);
}
FileOutputStream out = new FileOutputStream(new File(fpath));
workbook.write(out);
out.flush();
out.close();
System.out.println("文件:["+fpath+"] ["+sheets+"] 创建成功...");
} catch (Exception e) {
System.out.println("#Error ["+e.getMessage()+"] ");
}
System.out.println("");
}
}
以上是我在原先基础下做了一些修改,原先的如果导出文件夹不存在则直接报错导出失败,本人在里面增加了一个判断,如下:
//判断文件夹是否存在,不存在则创建一个
boolean isExit0=new File(path).exists();
if(!isExit0){
File folder=new File(path);
folder.setExecutable(true);
folder.setReadable(true);
folder.setWritable(true);
folder.mkdirs();
}
即如果文件夹不存在则创建一个该文件夹出来。
实例引用:
public static void main(String[] args) throws Throwable {
private static SimpleDateFormat sdf2 = new SimpleDateFormat("YYYY-MM-dd");
Object[] head = { "第一表格", "第二表格", "第三表格","第四表格"};
List<List<Object>> dataList = new ArrayList<List<Object>>();
List<Object> rowList = null;
for (int i = 0; i < 4; i++) {
rowList = new ArrayList<Object>();
for(int j=0;j<4;j++){
rowList.add("第"+i+"行第"+j+"列数据");
}
dataList.add(rowList);
}
String fileName = sdf2.format(new Date())+".xls";//获取当天日期为文件名称
String filePath = "c:/CZY/"; //文件路径
Excelutil.ExpExs(filePath,"",fileName,head,dataList);
}
大概就是这样,关于对excel工具类代码的理解,首先:
boolean isExist = new File(fpath).exists();
if(!isExist){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheets);
FileOutputStream out = new FileOutputStream(new File(fpath));
workbook.write(out);
out.flush();
out.close();
}
先判断文件是否存在,不存在则根据地址创建一个。
接着:
FileInputStream file = new FileInputStream(new File(fpath));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = null;
sheet = workbook.getSheetAt(0);
if(workbook.getSheet(sheets) == null){
sheet = workbook.createSheet(sheets); //+workbook.getNumberOfSheets()
}
接着在创建FileInputStream类的实例,根据FileInputStream类的实例生成HSSFWorkbook 类的实例,在获取HSSFWorkbook 类的实例的getSheetAt(0)函数值赋予HSSFSheet 的实例(以上原理有些复杂,本人目前尚未搞清楚,只能按照代码说了。。。)
接着往下就是设置样式和填充数据,各位看代码就好,就不一一详述,代码已经写得很清楚了。