package com.yc.action.grid;
|
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.math.BigDecimal;
|
import java.text.DecimalFormat;
|
import java.text.NumberFormat;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.Arrays;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.Iterator;
|
import java.util.List;
|
import java.util.Map;
|
|
import com.yc.utils.DateUtil;
|
import com.yc.utils.Page;
|
|
import jxl.Workbook;
|
import jxl.write.Label;
|
import jxl.write.WritableCellFormat;
|
import jxl.write.WritableSheet;
|
import jxl.write.WritableWorkbook;
|
import jxl.write.WriteException;
|
import jxl.write.biff.RowsExceededException;
|
|
/**
|
* Jxl 的 Excel写数据器.
|
* @author chenlb 2007-10-29 上午10:39:31
|
*/
|
public class JxlExcelWriter {
|
|
/**
|
* @param datas 封装着Object[]的列表, 一般是String内容.
|
* @param title 每个sheet里的标题. filed是需要导出的字段
|
* @throws ParseException
|
*/
|
public static void writeExcel(File file, Page page, String[] title,String[] filed,String[] panel_title,String[] panel_filed,List panel,String titles,String local) throws ParseException {
|
List datas=page.getData();
|
if(datas == null) {
|
throw new IllegalArgumentException("没有数据导出到excel!");
|
}
|
try {
|
WritableWorkbook workbook = Workbook.createWorkbook(file);
|
WritableSheet ws = workbook.createSheet("sheet 1", 0);
|
int rowNum = 0; //要写的行
|
int tempNum=0;
|
//写标题
|
Label cell = new Label(5, 0, titles);
|
ws.addCell(cell);
|
|
//--end
|
rowNum++;
|
if(panel!=null){
|
//1,写面板
|
if(panel_title != null) {
|
|
String[] s=local.split(";");
|
int samRow=0;
|
int temcol=0;
|
for(int i=0; i<panel_title.length; i++) {//按照面板位置写到excel
|
String[] st=s[i].split("#")[1].split(",");//位置
|
int col=0;
|
int row=rowNum+Integer.parseInt(st[0]);//行
|
if(samRow!=row)
|
temcol=0;
|
if(i>0)
|
col=temcol+Integer.parseInt(st[1]);//列
|
else
|
col=Integer.parseInt(st[1])-1;//列
|
Label lab = new Label(col,row , panel_title[i]);
|
ws.addCell(lab);//写入标题
|
//跟着要写入内容: 位置是col+1,row,
|
// int r = Integer.parseInt(st[2]);//TODO 处理跨行跨列
|
// int c = Integer.parseInt(st[3]) ;
|
// if( r != 0 || c != 0){ //合并单元格
|
// ws.mergeCells(cop,i,cop+c,r+i);
|
// cop += c;
|
// }
|
putRowC(ws, row, panel.get(0),new String[]{panel_filed[i]},col+1); //压一行到sheet
|
tempNum=row;
|
temcol=col+1;
|
samRow=row;
|
}
|
|
|
}
|
// for(int i=0; i<panel.size(); i++, rowNum++) {//写sheet
|
// putRowC(ws, rowNum, panel.get(i),panel_filed); //压一行到sheet
|
// }
|
|
//
|
if(panel_title != null)
|
rowNum=tempNum+2;
|
}
|
|
|
|
//---------格线内容
|
if(title != null) {
|
putRow(ws, rowNum, title);//压入标题
|
rowNum += 1;
|
}
|
for(int i=0; i<datas.size(); i++, rowNum++) {//写sheet
|
putRowC(ws, rowNum, datas.get(i),filed,0); //压一行到sheet
|
}
|
|
workbook.write();
|
workbook.close(); //一定要关闭, 否则没有保存Excel
|
} catch (RowsExceededException e) {
|
System.out.println("jxl write RowsExceededException: "+e.getMessage());
|
} catch (WriteException e) {
|
System.out.println("jxl write WriteException: "+e.getMessage());
|
} catch (IOException e) {
|
System.out.println("jxl write file i/o exception!, cause by: "+e.getMessage());
|
}
|
}
|
|
private static void putRow(WritableSheet ws, int rowNum, Object[] cells) throws RowsExceededException, WriteException {
|
for(int j=0; j<cells.length; j++) {//写一行
|
Label cell = new Label(j, rowNum, ""+cells[j]);
|
ws.addCell(cell);
|
}
|
}
|
private static void putRowC(WritableSheet ws, int rowNum, Object cells,String[] filed,int j) throws RowsExceededException, WriteException, ParseException {
|
HashMap<String,Object> map=(HashMap<String,Object>)cells;
|
boolean flg=false;
|
if(j>0) flg=true;
|
for(String s:filed){
|
String[] temp=s.split(";");
|
|
if(map.get(temp[0])!=null&&temp.length>1){//表示有格式
|
SimpleDateFormat formatDate = new SimpleDateFormat(temp[1].replaceAll("~", "-"));
|
if(DateUtil.isDate(formatDate.format(map.get(temp[0])))){//格式化日期
|
String st=formatDate.format(map.get(temp[0]));
|
Label cell = new Label(j, rowNum,st);
|
ws.addCell(cell);
|
}
|
else{
|
//格式化数字
|
|
int indx=temp[1].indexOf(".");
|
String format="#.0";
|
if(indx>0){//有小数位
|
//有千位
|
if(temp[1].startsWith(","))
|
format=temp[1].replaceAll(",0.*\\.", "#,###.");
|
else
|
format=temp[1].replaceAll("0.*\\.", "#.");
|
}else{//整数
|
format=temp[1].replaceAll("0", "#"); //是由于格线与java对于格式化的不同，需要在这里做转换
|
}
|
|
if(map.get(temp[0]) instanceof Double){
|
Double d=(Double)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format);
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN);
|
ws.addCell(cell);
|
}else if(map.get(temp[0]) instanceof BigDecimal){
|
BigDecimal d=(BigDecimal)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format);
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN);
|
ws.addCell(cell);
|
}
|
else if(map.get(temp[0]) instanceof Float){
|
Float d=(Float)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format);
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN);
|
ws.addCell(cell);
|
}else if(map.get(temp[0]) instanceof Integer){
|
Integer d=(Integer)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#");
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN);
|
ws.addCell(cell);
|
}
|
}
|
}else{
|
//普通字符
|
|
if(map.get(temp[0]) instanceof Double){
|
Double d=(Double)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.00");
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN);
|
ws.addCell(cell);
|
}else if(map.get(temp[0]) instanceof BigDecimal){
|
BigDecimal d=(BigDecimal)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.00");
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN);
|
ws.addCell(cell);
|
}
|
else if(map.get(temp[0]) instanceof Float){
|
Float d=(Float)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.00");
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN);
|
ws.addCell(cell);
|
}else if(map.get(temp[0]) instanceof Integer){
|
Integer d=(Integer)map.get(temp[0]);
|
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#");
|
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
|
jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN);
|
ws.addCell(cell);
|
}else{
|
Label cell = new Label(j, rowNum, ""+((map.get(temp[0])==null||"null".equals(map.get(temp[0])))?"":map.get(temp[0])));
|
ws.addCell(cell);
|
}
|
}
|
if(!flg) j++;
|
}
|
}
|
}
|
