java jxl excelutil_ExeclUtil 工具类（poi + jxl）

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;

}

}