Excel工具类

publicclassExcelUtil{
/**
*
*方法说明按照xml配置模版导出数据到excel
*
*@paramdataClass
*数据实体类类型
*@paramdataList
*数据集合
*@paramimportKey
*xml配置路径枚举
*@paramresponse
*web响应对象
*/
publicstaticvoidexportExcel(Class<Object>dataClass,List<Object>dataList,ExcelToTableConfigEnumimportKey,HttpServletResponseresponse)
throwsWriteException,NoSuchFieldException,SecurityException,IllegalArgumentException,IllegalAccessException,IOException,DocumentException{
ExcelToTableConfigexcelConfig=getTableConfig(importKey);
OutputStreamout=null;
response.reset();//清空输出流
response.setHeader("Content-disposition","attachment;filename="+excelConfig.getChineseName()+".xls");//设定输出文件头
response.setContentType("application/msexcel");//定义输出类型
out=response.getOutputStream();
//创建工作簿
WritableWorkbookworkbook=null;
workbook=Workbook.createWorkbook(out);
intsheetRows=excelConfig.getMaxSheetRows();
intsheetNumber=1;
if(dataList.size()%sheetRows>0){
sheetNumber=dataList.size()/sheetRows+1;
}else{
sheetNumber=dataList.size()/sheetRows;
}
for(intsn=0;sn<sheetNumber&&dataList.size()>0;sn++){
//创建工作页
WritableSheetsheet=workbook.createSheet("sheet"+(sn+1),sn);
//生成表头
createExcelTitle(excelConfig,sheet);
//写入表数据
writeExcelData(sn,sheet,dataClass,dataList,excelConfig);
}
workbook.write();//写入文件
workbook.close();
out.close();
}
/**
*
*方法说明读取xml配置
*
*@paramexcelToTableConfig
*@return
*/
publicstaticExcelToTableConfiggetTableConfig(ExcelToTableConfigEnumexcelToTableConfig)throwsDocumentException,IOException{
//将解析结果存入HashMap中
Map<String,Object>map=newHashMap<String,Object>();
InputStreamin=null;
Stringpath=Class.class.getClassLoader().getResource(excelToTableConfig.getConfigUrl()).getPath();
in=newFileInputStream(path);
//读取输入流
SAXReaderreader=newSAXReader();
Documentdocument=null;
document=reader.read(in);
//得到xml根元素
Elementtable=document.getRootElement();
ExcelToTableConfigconfig=newExcelToTableConfig();
config.setTableName(table.attributeValue("tableName"));
config.setStartRow(Integer.parseInt(table.attributeValue("startRow")));
config.setChineseName(table.attributeValue("chineseName"));
//得到所有子节点
List<Element>colList=table.elements();
//便利所有子节点
List<Column>columnList=newArrayList<Column>();
for(Elementcol:colList){
Columncolumn=newColumn();
column.setIndex(Integer.parseInt(col.attributeValue("index")));
column.setColName(col.attributeValue("colName"));
column.setColType(col.attributeValue("colType"));
column.setIsHidden(col.attributeValue("isHidden"));
column.setIsWhere(col.attributeValue("isWhere"));
column.setChineseName(col.attributeValue("chineseName"));
column.setLength(Integer.parseInt(col.attributeValue("length")));
columnList.add(column);
}
config.setColumnList(columnList);
//释放资源
in.close();
in=null;
returnconfig;
}

/**
*
*方法说明导出数据到excel
*
*@paramsn
*第几个sheet
*@paramsheet
*当前sheet对象
*@paramdataClass
*数据实体类类型
*@paramdataList
*数据集合
*@paramexcelConfig
*导出配置
*/
privatestaticvoidwriteExcelData(intsn,WritableSheetsheet,Class<Object>dataClass,ExcelToTableConfigexcelConfig)
throwsWriteException,IllegalAccessException{
intmaxSheetRows=excelConfig.getMaxSheetRows();
intexcelDataStartRow=excelConfig.getStartRow();
for(intcurrentRow=excelDataStartRow;currentRow<=maxSheetRows&&sn*(maxSheetRows-excelDataStartRow)+currentRow<dataList.size();currentRow++){//行
ObjectdataObj=dataList.get(sn*(maxSheetRows-excelDataStartRow)+currentRow-1);
//写入行数据
writeExcelRowData(currentRow,excelConfig,dataObj,sheet);
}
}

/**
*
*方法说明生成excel行数据
*
*@paramcurrentRow
*当前行号
*@paramexcelConfig
*导出配置
*@paramdataClass
*数据实体类类型
*@paramdataObj
*数据实体实例
*@paramsheet
*当前sheet
*/
privatestaticvoidwriteExcelRowData(intcurrentRow,ExcelToTableConfigexcelConfig,ObjectdataObj,WritableSheetsheet)
throwsWriteException,IllegalAccessException{
WritableFontfont=newWritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
List<Column>colList=excelConfig.getColumnList();
for(inti=0;i<colList.size();i++){
Columncol=colList.get(i);
StringfieldName=col.getFieldName();
Fieldfield=dataClass.getDeclaredField(fieldName);
field.setAccessible(true);
WritableCelllabel=null;
if(YesOrNoEnum.Yes.name().equals(col.getIsHidden())){
sheet.setColumnView(i,0);
}
Objectvalue=field.get(dataObj);
if(long.class.getName().equals(col.getColType())){
//整型数字样式
WritableCellFormatformat=newWritableCellFormat(font,NumberFormats.INTEGER);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
format.setBorder(Border.ALL,BorderLineStyle.THIN);
if(YesOrNoEnum.Yes.name().equals(col.getReadOnly())){
format.setLocked(true);
}
label=newjxl.write.Number(i,currentRow,(long)value,format);
}elseif(String.class.getSimpleName().equals(col.getColType())){
WritableCellFormatformat=newWritableCellFormat(font);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
format.setBorder(Border.ALL,BorderLineStyle.THIN);
if(YesOrNoEnum.Yes.name().equals(col.getReadOnly())){
format.setLocked(true);
}
label=newLabel(i,(String)value,format);
}
field.setAccessible(false);
sheet.addCell(label);
}
}

/**
*
*方法说明导出excel表头
*
*@paramexcelConfig导出配置
*@paramsheet当前sheet
*/
publicstaticvoidcreateExcelTitle(ExcelToTableConfigexcelConfig,WritableSheetsheet)throwsWriteException{
//表头文本样式
//字符串样式
WritableFontfont=newWritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,Colour.BLACK);
WritableCellFormatformat=newWritableCellFormat(font);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
format.setBackground(Colour.GREEN);
format.setBorder(Border.ALL,BorderLineStyle.THIN);
List<Column>colList=excelConfig.getColumnList();
for(inti=0;i<colList.size();i++){
Columncol=colList.get(i);
//设置列宽
sheet.setColumnView(i,col.getLength());//第一列
//写入表头
Labellabel=newLabel(i,col.getChineseName(),format);
sheet.addCell(label);
}
}
/**
*
*方法说明生成一条更新语句
*
*@paramcolsexcel导入字段
*@paramtableName表名
*@paramcolumnList表字段配置
*@return
*/
privatestaticStringgetOneUpatesql(Cell[]cols,StringtableName,List<Column>columnList){
StringBufferupdateStatement=newStringBuffer("update"+tableName+"set");
StringBufferwhereStatement=newStringBuffer("where");
for(intindex=0;index<cols.length;index++){
Cellcell=cols[index];
StringcolValue=cell.getContents();
Columncolumn=null;
for(Columncol:columnList){
if(index==col.getIndex()){
column=col;
}
}
if(YesOrNoEnum.Yes.name().equals(column.getIsWhere())){
if(!"where".equals(whereStatement.toString())){
whereStatement.append("and");
}
getKeyValue(colValue,column,whereStatement);
}else{
if(!("update"+tableName+"set").equals(updateStatement.toString())){
updateStatement.append(",");
}
getKeyValue(colValue,updateStatement);
}
}
returnupdateStatement.append(whereStatement.toString()).toString();
}
/**
*
*方法说明生成columnName=colValue键值对
*
*@paramcolValue值
*@paramcolumn列
*@paramsql
*/
privatestaticvoidgetKeyValue(StringcolValue,Columncolumn,StringBuffersql){
StringcolName=column.getColName();
if(long.class.getName().equals(column.getColType())){
sql.append(colName+"="+colValue);
}elseif(String.class.getName().equals(column.getColType())){
sql.append(colName+"='"+colValue+"'");
}
}
/**
*
*方法说明导入excel生成更新语句集
*
*@paramuploadfileexcel文件
*@paramimportKeyexcel对应xml配置
*@return
*/
publicstaticList<String>getImportsql(MultipartFileuploadfile,ExcelToTableConfigEnumimportKey)throwsBiffException,DocumentException{
Workbookbook=null;
InputStreamin=null;
List<String>sqlList=newArrayList<String>();
book=Workbook.getWorkbook(uploadfile.getInputStream());
//获得第一个工作表对象
Sheetsheet=book.getSheet(0);
introws=sheet.getRows();
ExcelToTableConfigtableConfig=getTableConfig(importKey);
//遍历每行
StringtableName=tableConfig.getTableName();
intstartRowNumber=tableConfig.getStartRow();
List<Column>columnList=tableConfig.getColumnList();
for(inti=startRowNumber;i<rows;i++){
Cell[]row=sheet.getRow(i);
//遍历每列
Stringsql=getOneUpatesql(row,tableName,columnList);
sqlList.add(sql);
}
if(book!=null){
book.close();
}
returnsqlList;
}
}