从Grid 导出Excel3

package com.cnc.erp.util;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.cnc.erp.cfg.Globals;
import com.cnc.erp.fm.FmConstants;
import com.liferay.portal.kernel.util.Validator;

@SuppressWarnings("unchecked")
public class ExcelUtil
{
//列号字母转换--用于计算列
public static final String[] head ={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI"};

/**
* 生成计算列
* sheet为Excel当前sheet
* startrow为合计列开始行号
* endrow为合计列结束行号
* computerow为合计列行号
* compute为计算列列号
*/
public static void genComputColumn(WritableSheet sheet,int startrow,int endrow,int computerow,int compute[]) throws RowsExceededException, WriteException
{
//5个参数必须有效
if(sheet==null)
return;
if(startrow<1)
return;
if(endrow<1)
return;
if(computerow<2)
return;
if(compute==null || compute.length==0)
return;
//生成合计字段
jxl.write.Label label;
label = new jxl.write.Label(0,computerow,"合计");
sheet.addCell(label);
//生成合计字段,定义合计列格式
int size = compute.length;
for(int i=0;i<size;i++)
{
/定义合计函数
String formu = "SUM("+head[compute[i]]+startrow+":"+head[compute[i]]+endrow+")";
Formula formula = new Formula(compute[i], computerow, formu);
sheet.addCell(formula);
}

}

/**
* 导出Excel文件
* @param lst 要导出的数据信息
* @param columnNameLs 显示的列名
* @param columnIDLs 要导出的列ID
* @param columnWidthLs 显示的列宽
* @param columnTypeLs 显示的列类型
* @param columnTotalLs 是否合计的列字段
* @param showDisplayValueMap 是否需要转换的列信息
* @param filterDataMap 要过滤的行记录条件
* @return
* @throws Exception
*/
public static File exportToExcel(List<Map> lst,String columnNameLs,
String columnIDLs, String columnWidthLs, String columnTypeLs,
String columnTotalLs, Map<String,String> showDisplayValueMap,
Map<String,String> filterDataMap) throws Exception {
File rst;
String tempBasePath = EnvionmentUtil.getTempPath();
String filename = UtilFile.setupRandomFile(tempBasePath,FmConstants.XLS_TYPE);
FileOutputStream fos = new FileOutputStream(filename);
WritableWorkbook wb = Workbook.createWorkbook(fos);
WritableSheet wSheet = wb.createSheet("sheet1", 0);
Iterator<Map> ite = lst.iterator();

//Excel的列头信息格式
int row = 1;
WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.CENTRE);

//日期格式的列
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");
WritableCellFormat wcfDF = new WritableCellFormat(df);
//数字格式的列
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.####");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.WritableCell writableCell ;

//定义列宽,列名
String[] columnName = columnNameLs .split(",");
String[] columnID = columnIDLs .split(",");
String[] columnWidth = columnWidthLs .split(",");
String[] columnType = columnTypeLs .split(",");
String[] columnTotal = columnTotalLs .split(",");

int size = columnName.length;
//写列头
for(int i=0;i<size;i++)
{
wSheet.setColumnView(i,Integer.valueOf(columnWidth[i])+10);
wSheet.addCell(new Label(i,0,columnName[i],wcf));
}

写列内容

while (ite.hasNext())
{
Map<String, String> item = ite.next();
if(!ifFilterRowRecord(filterDataMap, item)){
for(int i=0;i<size;i++)
{
String itemValue = getItemValue(showDisplayValueMap,columnID[i],item.get(columnID[i]));

if(Globals.INT.equals(columnType[i]) && Validator.isNotNull(itemValue)){
writableCell = new jxl.write.Number(i, row, Double.valueOf(itemValue), wcfN);
}
//数字如果为空设置为0.0
else if(Globals.INT.equals(columnType[i]) && Validator.isNull(itemValue)){
writableCell = new jxl.write.Number(i, row, 0.0D, wcfN);
}
else if(Globals.DATE.equals(columnType[i]) && Validator.isNotNull(itemValue)){
writableCell = new DateTime(i, row, DateUtil.convertStringToDate(itemValue),wcfDF);
}
else{
writableCell = new Label(i, row, itemValue);
}
wSheet.addCell(writableCell);

}

row++;
}
}

//写合计列
if(columnTotal.length>0 && row>1)
{
for(int i=0; i<columnTotal.length; i++){
if(Globals.YES.toString().equals(columnTotal[i])){
//定义合计函数
String formu = "SUM("+head[i]+"2:"+head[i]+row+")";
Formula formula = new Formula(i, row, formu);
wSheet.addCell(formula);
}
}
}

try{
wb.write();
rst = new File(filename);
}
finally{
wb.close();
fos.close();
}

return rst;
}



/**
* 收款跟踪导出带合并行的Excel文件
* @param lst 要导出的数据信息
* @param columnNameLs 显示的列名
* @param columnIDLs 要导出的列ID
* @param columnWidthLs 显示的列宽
* @param columnTypeLs 显示的列类型
* @param columnTotalLs 是否合计的列字段
* @param showDisplayValueMap 是否需要转换的列信息
* @param filterDataMap 要过滤的行记录条件
* @return
* @throws Exception
*/
public static File hbexportToExcel(List<Map> lst,String columnNameLs,
String columnIDLs, String columnWidthLs, String columnTypeLs,
String columnTotalLs, Map<String,String> showDisplayValueMap,
Map<String,String> filterDataMap) throws Exception {
File rst;
String tempBasePath = EnvionmentUtil.getTempPath();
String filename = UtilFile.setupRandomFile(tempBasePath,FmConstants.XLS_TYPE);
FileOutputStream fos = new FileOutputStream(filename);
WritableWorkbook wb = Workbook.createWorkbook(fos);
WritableSheet wSheet = wb.createSheet("sheet1", 0);
Iterator<Map> ite = lst.iterator();


//Excel的列头信息格式
int row = 1;
WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.CENTRE);



//日期格式的列
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");
WritableCellFormat wcfDF = new WritableCellFormat(df);
//数字格式的列
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.####");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.WritableCell writableCell ;

//Excel的合并单元格信息
WritableCellFormat wcfhbcell = new WritableCellFormat();
wcfhbcell.setVerticalAlignment(VerticalAlignment.CENTRE);
wcfN.setVerticalAlignment(VerticalAlignment.CENTRE);
wcfDF.setVerticalAlignment(VerticalAlignment.CENTRE);
//wcfhbcell.setAlignment(Alignment.CENTRE);

//定义列宽,列名
String[] columnName = columnNameLs .split(",");
String[] columnID = columnIDLs .split(",");
String[] columnWidth = columnWidthLs .split(",");
String[] columnType = columnTypeLs .split(",");
String[] columnTotal = columnTotalLs .split(",");

int size = columnName.length;
//写列头
for(int i=0;i<size;i++)
{
wSheet.setColumnView(i,Integer.valueOf(columnWidth[i])+10);
wSheet.addCell(new Label(i,0,columnName[i],wcf));
}

写列内容

Map <String, String> item = null;
for(int i=0;i<size;i++)
{
//id2必须写在for循环里面，不然1条记录的话就会出错
String id2="0";
row=1;
int a=1;
while (ite.hasNext())
{
item = ite.next();

if(!ifFilterRowRecord(filterDataMap, item)){
String itemValue = getItemValue(showDisplayValueMap,columnID[i],item.get(columnID[i]));
String Value = getItemValue(showDisplayValueMap,columnID[0],item.get(columnID[0]));

String[] aa=Value.split(",");
String id=aa[0];
if(id.equals(id2))
{
a=a+1;
if(i>10&&i<15)
{

if(Globals.INT.equals(columnType[i]) && Validator.isNotNull(itemValue)){
writableCell = new jxl.write.Number(i, row,Double.valueOf(itemValue),wcfN);
}
else if(Globals.DATE.equals(columnType[i]) && Validator.isNotNull(itemValue)){
writableCell = new DateTime(i, row, DateUtil.convertStringToDate(itemValue),wcfDF);
}
else{
writableCell = new Label(i, row, itemValue);
}
wSheet.addCell(writableCell);
}
//判断是否是最后一条数据，对最后一条主数据进行合并
if(row==lst.size()&&(i<10||i>13))
{

wSheet.mergeCells(i+1,row-a+1,i+1,row);//左上角到右下角


}


}
else
{
if(i<10||i>13)
{
wSheet.mergeCells(i+1,row-a,i+1,row-1);//左上角到右下角
}
a=1;

if(Globals.INT.equals(columnType[i]) && Validator.isNotNull(itemValue)){
writableCell = new jxl.write.Number(i, row,Double.valueOf(itemValue),wcfN);
writableCell.setCellFormat(wcfN);
}
else if(Globals.DATE.equals(columnType[i]) && Validator.isNotNull(itemValue)){
writableCell = new DateTime(i, row, DateUtil.convertStringToDate(itemValue),wcfDF);
writableCell.setCellFormat(wcfDF);
}
else{
writableCell = new Label(i, row, itemValue);
writableCell.setCellFormat(wcfhbcell);
}
wSheet.addCell(writableCell);
}

id2=id;
row++;


}
}

ite = lst.iterator();
}

//写合计列
if(columnTotal.length>0 && row>1)
{
for(int i=0; i<columnTotal.length; i++){
if(Globals.YES.toString().equals(columnTotal[i])){
//定义合计函数
String formu = "SUM("+head[i]+"2:"+head[i]+row+")";
Formula formula = new Formula(i, row, formu);
wSheet.addCell(formula);
}
}
}

try{
wb.write();
rst = new File(filename);
}
finally{
wb.close();
fos.close();
}

return rst;
}


public static File exportToExcel(List<Map> lst,String columnNameLs,
String columnIDLs, String columnWidthLs, String columnTypeLs,
String columnTotalLs) throws Exception {
return exportToExcel(lst, columnNameLs, columnIDLs, columnWidthLs,
columnTypeLs, columnTotalLs, null);
}

public static File exportToExcel(List<Map> lst,String columnNameLs,
String columnIDLs, String columnWidthLs, String columnTypeLs,
String columnTotalLs, Map<String,String> showDisplayValueMap) throws Exception {
return exportToExcel(lst, columnNameLs, columnIDLs, columnWidthLs,
columnTypeLs, columnTotalLs, showDisplayValueMap, null);
}

public static File hbexportToExcel(List<Map> lst,String columnNameLs,
String columnIDLs, String columnWidthLs, String columnTypeLs,
String columnTotalLs, Map<String,String> showDisplayValueMap) throws Exception {
return hbexportToExcel(lst, columnNameLs, columnIDLs, columnWidthLs,
columnTypeLs, columnTotalLs, showDisplayValueMap, null);
}
/**
* 获取列的值
* @param showDisplayValueMap 要转换为参数表或单位表Display的列
* @param columnID 列ID
* @param itemValue 要转换的值
* @return
*/
private static String getItemValue(Map<String, String> showDisplayValueMap, String columnID,
Object itemValue) {
if(Validator.isNull(itemValue)){
return "";
}

if(Validator.isNotNull(showDisplayValueMap) &&
showDisplayValueMap.containsKey(columnID)){
String value = showDisplayValueMap.get(columnID);
if(Validator.isNotNull(value)){
itemValue = CacheUtil.getComDisplayValue(showDisplayValueMap.get(columnID), String.valueOf(itemValue));
}
else{
itemValue = CacheUtil.getUnitName(Long.parseLong(String.valueOf(itemValue)));
}
}
return String.valueOf(itemValue);
}

/**
* 是否过滤行记录
* @param filterDataMap 要过滤的字段ID和值信息
* @param item 字段ID和值信息
* @return
*/
private static boolean ifFilterRowRecord(Map<String, String> filterDataMap,
Map<String, String> item) {
if(Validator.isNotNull(filterDataMap)){
Set<String> keySet = filterDataMap.keySet();
for(String key : keySet){
if(filterDataMap.get(key).equals(item.get(key))){
return true;
}
}
}
return false;
}
}