importjava.io.BufferedInputStream;importjava.io.File;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.io.UnsupportedEncodingException;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importjava.util.Map;importjavax.servlet.http.HttpServletResponse;importjxl.Cell;importjxl.CellType;importjxl.FormulaCell;importjxl.NumberCell;importjxl.Sheet;importjxl.SheetSettings;importjxl.Workbook;importjxl.format.Alignment;importjxl.format.Colour;importjxl.format.VerticalAlignment;importjxl.read.biff.BiffException;importjxl.write.Label;importjxl.write.WritableCellFormat;importjxl.write.WritableFont;importjxl.write.WritableSheet;importjxl.write.WritableWorkbook;importnet.sf.jxls.exception.ParsePropertyException;importnet.sf.jxls.transformer.XLSTransformer;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;importorg.apache.poi.ss.usermodel.CellValue;importorg.apache.poi.ss.usermodel.FormulaEvaluator;/*** @ClassName: ExcelUtil
* @Description: jxl操作excel的工具类*/
public classExcelUtil {private static DecimalFormat decimalFormat = new DecimalFormat("###################.###########");/*** @Title: exportToExcel
* @Description: 导出excel
*@paramresponse
*@paramobjData
* 导出内容数组
*@paramsheetName
* 导出工作表的名称
*@paramheads
* 列对应的数据在Map中的key
*@paramcolumns
* 导出Excel的表头数组
*@return
*/
public static intexportToExcel(HttpServletResponse response,
List>objData, String sheetName,
List columns, Listheads) {int flag = 0;//声明工作簿jxl.write.WritableWorkbook
WritableWorkbook wwb;try{//根据传进来的file对象创建可写入的Excel工作薄
OutputStream os =response.getOutputStream();
wwb=Workbook.createWorkbook(os);/** 创建一个工作表、sheetName为工作表的名称、"0"为第一个工作表
* 打开Excel的时候会看到左下角默认有3个sheet、"sheet1、sheet2、sheet3"
* 这样代码中的"0"就是sheet1、其它的一一对应 createSheet(sheetName,
* 0)一个是工作表的名称,另一个是工作表在工作薄中的位置*/WritableSheet ws= wwb.createSheet(sheetName, 0);
SheetSettings ss=ws.getSettings();
ss.setVerticalFreeze(1);//冻结表头
WritableFont font1= newWritableFont(
WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD);
WritableFont font2= newWritableFont(
WritableFont.createFont("微软雅黑"), 9, WritableFont.NO_BOLD);
WritableCellFormat wcf= newWritableCellFormat(font1);
WritableCellFormat wcf2= newWritableCellFormat(font2);
WritableCellFormat wcf3= new WritableCellFormat(font2);//设置样式,字体//创建单元格样式//WritableCellFormat wcf = new WritableCellFormat();//背景颜色
wcf.setBackground(jxl.format.Colour.YELLOW);
wcf.setAlignment(Alignment.CENTRE);//平行居中
wcf.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直居中
wcf3.setAlignment(Alignment.CENTRE); //平行居中
wcf3.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直居中
wcf3.setBackground(Colour.LIGHT_ORANGE);
wcf2.setAlignment(Alignment.CENTRE);//平行居中
wcf2.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直居中
/** 这个是单元格内容居中显示 还有很多很多样式*/wcf.setAlignment(Alignment.CENTRE);//判断一下表头数组是否有数据
if (columns != null && columns.size() > 0) {//循环写入表头
for (int i = 0; i < columns.size(); i++) {/** 添加单元格(Cell)内容addCell() 添加Label对象Label()
* 数据的类型有很多种、在这里你需要什么类型就导入什么类型 如:jxl.write.DateTime
* 、jxl.write.Number、jxl.write.Label Label(i, 0, columns[i],
* wcf) 其中i为列、0为行、columns[i]为数据、wcf为样式
* 合起来就是说将columns[i]添加到第一行(行、列下标都是从0开始)第i列、样式为什么"色"内容居中*/ws.addCell(new Label(i, 0, columns.get(i), wcf));
}//判断表中是否有数据
if (objData != null && objData.size() > 0) {//循环写入表中数据
for (int i = 0; i < objData.size(); i++) {//转换成map集合{activyName:测试功能,count:2}
Map map = (Map) objData
.get(i);//循环输出map中的子集:既列值
for (int j = 0; j < heads.size(); j++) {
ws.addCell(new Label(j, i + 1, String.valueOf(map
.get(heads.get(j))== null ? "": map
.get(heads.get(j)))));
}
}
}else{
flag= -1;
}//写入Exel工作表
wwb.write();//关闭Excel工作薄对象
wwb.close();//关闭流
os.flush();
os.close();
os= null;
}
}catch(IllegalStateException e) {
System.err.println(e.getMessage());
}catch(Exception ex) {
flag= 0;
ex.printStackTrace();
}returnflag;
}/*** @Title: exportexcle
* @Description: 下载excel
*@paramresponse
*@paramfilename
* 文件名 ,如:20110808.xls
*@paramlistData
* 数据源
*@paramsheetName
* 表头名称
*@paramheads
* 列对应的数据在Map中的key
*@paramcolumns
* 列名称集合,如:{物品名称,数量,单价}*/
public static voidexportexcle(HttpServletResponse response,
String filename, List>listData,
String sheetName, List columns, Listheads) {//调用上面的方法、生成Excel文件
response.setContentType("application/vnd.ms-excel");try{
response.setHeader("Content-Disposition", "attachment;filename="
//+ new String(filename.getBytes("gb2312"), "ISO8859-1")
+ filename + ".xls");
exportToExcel(response, listData, sheetName, columns, heads);
}catch(Exception e) {
e.printStackTrace();
}
}/*** @Title: exportExcleByTemplate
* @Description: 根据模板导出Excel文件
*@paramresponse
*@paramtemplateFilePath 模板文件路径包括模板名称和扩展名
*@parambeanParams 导出数据
*@paramresultFileName 导出文件名称和扩展名
*@throwsUnsupportedEncodingException*/
public static void exportExcleByTemplate(HttpServletResponse response, String templateFilePath, Map beanParams, String resultFileName) throwsUnsupportedEncodingException {//设置响应
response.setHeader("Content-Disposition", "attachment;filename=" + new String(resultFileName.getBytes("UTF-8"), "ISO8859-1"));
response.setContentType("application/vnd.ms-excel");//创建XLSTransformer对象
XLSTransformer transformer = newXLSTransformer();
InputStream in=null;
OutputStream out=null;try{
in=new BufferedInputStream(newFileInputStream(templateFilePath));
org.apache.poi.ss.usermodel.Workbook workbook=transformer.transformXLS(in, beanParams);
out=response.getOutputStream();//将内容写入输出流并把缓存的内容全部发出去
workbook.write(out);
out.flush();
}catch(InvalidFormatException e) {
e.printStackTrace();
}catch(IOException e) {
e.printStackTrace();
}finally{if (in!=null){try {in.close();} catch(IOException e) {}}if (out!=null){try {out.close();} catch(IOException e) {}}
}
}/*** @Title: createExcel
* @Description: 根据模板生成Excel文件
*@paramsrcFilePath 模板文件路径
*@parambeanParams 模板中存放的数据
*@paramdestFilePath 生成的文件路径*/
public static void createExcel(String srcFilePath, MapbeanParams, String destFilePath){//创建XLSTransformer对象
XLSTransformer transformer = newXLSTransformer();try{//生成Excel文件
transformer.transformXLS(srcFilePath, beanParams, destFilePath);
}catch(ParsePropertyException e) {
e.printStackTrace();
}catch(IOException e) {
e.printStackTrace();
}catch(InvalidFormatException e) {
e.printStackTrace();
}
}/*** @Title: readExcel
* @Description: 读取Excel文件的内容
*@paramfile
* 待读取的文件
*@return
*/
public static List>>readExcel(File file)throwsBiffException, IOException {
Workbook workBook=Workbook.getWorkbook(file);if (workBook == null)return null;returngetDataInWorkbook(workBook);
}private static List>>getDataInWorkbook(Workbook workBook) {//获得了Workbook对象之后,就可以通过它得到Sheet(工作表)对象了
Sheet[] sheet =workBook.getSheets();
List>> dataList = new ArrayList>>();if (sheet != null && sheet.length > 0) {//对每个工作表进行循环
for (int i = 0; i < sheet.length; i++) {
List> rowList = new ArrayList>();//得到当前工作表的行数
int rowNum =sheet[i].getRows();int colNum =sheet[i].getColumns();for (int j = 0; j < rowNum; j++) {//得到当前行的所有单元格
Cell[] cells =sheet[i].getRow(j);if (cells != null && cells.length > 0) {
List cellList = new ArrayList();//对每个单元格进行循环
for (int k = 0; k < colNum; k++) {
Cell cell=sheet[i].getCell(k, j);
String cellValue= "";//判断单元格的值是否是数字
if (cell.getType() ==CellType.NUMBER) {
NumberCell numberCell=(NumberCell) cell;double value =numberCell.getValue();
cellValue=decimalFormat.format(value);
}else if (cell.getType() ==CellType.NUMBER_FORMULA|| cell.getType() ==CellType.STRING_FORMULA|| cell.getType() ==CellType.BOOLEAN_FORMULA|| cell.getType() ==CellType.DATE_FORMULA|| cell.getType() ==CellType.FORMULA_ERROR) {
FormulaCell nfc=(FormulaCell) cell;
cellValue=nfc.getContents();
}else{//读取当前单元格的值
cellValue =cell.getContents();//特殊字符处理
cellValue =excelCharaterDeal(cellValue);
}//去掉空格
cellList.add(cellValue.trim());
}
rowList.add(cellList);
}
}
dataList.add(rowList);
}
}//最后关闭资源,释放内存
workBook.close();returndataList;
}/*** @Title: toToken
* @Description: 除去字符串中指定的分隔符
*@params
* 字符串
*@paramval
* 指定的分隔符
*@return
*/
private staticString toToken(String s, String val) {if (s == null || s.trim().equals("")) {returns;
}if (val == null || val.equals("")) {returns;
}
StringBuffer stringBuffer= newStringBuffer();
String[] result=s.split(val);for (int x = 0; x < result.length; x++) {
stringBuffer.append(" ").append(result[x]);
}returnstringBuffer.toString();
}/*** @Title: excelCharaterDeal
* @Description: Excel特殊字符处理
*@paramstr
* 字符串
*@return
*/
private staticString excelCharaterDeal(String str) {
String[] val= { "-", "_", "/" };//定义特殊字符
for(String i : val) {
str=toToken(str, i);
}returnstr;
}/*** @Title: readExcelTitle
* @Description: 读取Excel表格表头的内容
*@paramfile 待读取的文件
*@return表头内容的数组
*@throwsException*/
public String[] readExcelTitle(File file) throwsException {
InputStream is= newFileInputStream(file);
HSSFWorkbook wb= newHSSFWorkbook(is);
HSSFSheet sheet= wb.getSheetAt(0);
HSSFRow row= sheet.getRow(0);//标题总列数
int colNum =row.getPhysicalNumberOfCells();
String[] title= newString[colNum];for (int i = 0; i < colNum; i++) {
title[i]=getCellFormatValue(row.getCell(i));
}returntitle;
}/*** @Title: readExcelByPoi
* @Description: 通过POI读取Excel文件的内容
*@paramfile 待读取的文件
*@return*@throwsException*/
public static List>> readExcelByPoi(File file) throwsException {
List>> dataList = new ArrayList>>();
InputStream is= newFileInputStream(file);
HSSFWorkbook wb= newHSSFWorkbook(is);int sheetNum =wb.getNumberOfSheets();for (int i = 0; i < sheetNum; i++) {
HSSFSheet sheet=wb.getSheetAt(i);if (sheet == null) {continue;
}//得到总行数
int rowNum =sheet.getLastRowNum();
HSSFRow row= sheet.getRow(0);if (row == null) {continue;
}//得到总列数
int colNum =row.getPhysicalNumberOfCells();
List> rowList = new ArrayList>();//循环行Row
for (int j = 0; j <= rowNum; j++) {
row=sheet.getRow(j);if (row == null) {continue;
}
List cellList = new ArrayList();int k = 0;while (k
String cellValue=getCellFormatValue(row.getCell(k)).trim();
cellList.add(cellValue);
k++;
}
rowList.add(cellList);
}
dataList.add(rowList);
}returndataList;
}/*** @Title: getCellFormatValue
* @Description: 获取单元格数据内容为字符串类型的数据
*@paramcell Excel单元格
*@return单元格数据内容*/
private staticString getCellFormatValue(HSSFCell cell) {
String cellvalue= "";if (cell != null) {//判断当前Cell的Type
switch(cell.getCellType()) {//如果当前Cell的Type为NUMERIC
caseHSSFCell.CELL_TYPE_NUMERIC://取得当前Cell的数值
cellvalue =decimalFormat.format(cell.getNumericCellValue());break;caseHSSFCell.CELL_TYPE_FORMULA: {//判断当前的cell是否为Date
if(HSSFDateUtil.isCellDateFormatted(cell)) {//如果是Date类型则,转化为Data格式//方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00//cellvalue = cell.getDateCellValue().toLocaleString();//方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date =cell.getDateCellValue();
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
cellvalue=sdf.format(date);
}else{
FormulaEvaluator evaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
CellValue cellValue=evaluator.evaluate(cell);switch(cellValue.getCellType()) {caseorg.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
cellvalue=String.valueOf(cellValue.getBooleanValue());break;caseorg.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
cellvalue=decimalFormat.format(cellValue.getNumberValue());break;caseorg.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
cellvalue=String.valueOf(cellValue.getStringValue());break;caseorg.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:break;caseorg.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:break;
}
}break;
}//如果当前Cell的Type为STRIN
caseHSSFCell.CELL_TYPE_STRING://取得当前的Cell字符串
cellvalue =cell.getRichStringCellValue().getString();break;//默认的Cell值
default:
cellvalue= "";
}
}else{
cellvalue= "";
}returncellvalue;
}
}