jxl导出excel

/**

* 统一设置导出格式

* 

* @param res

*/

public WritableSheet makeExcel(WritableWorkbook wbook,

HttpServletResponse res, String title, int n, String zy,

String customer) {

try {

res.setContentType("application/msexcel;charset=GBK");// 定义输出类型

res.setHeader("Content-Disposition", "attachment; filename="

+ new String(zy.getBytes("GBK"), "ISO8859-1") + "("

+ sf.format(new Date()) + ")" + ".xls");// 设定输出文件头

if (null != customer && "settlement".equals(customer)) {

headerFont = new WritableFont(WritableFont.createFont("新宋体"),

8, WritableFont.BOLD, false,

UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

} else {

headerFont = new WritableFont(WritableFont.createFont("新宋体"),

10, WritableFont.BOLD, false,

UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

}

headerFormat = new WritableCellFormat(headerFont);

if (null != customer && "settlement".equals(customer)) {

dataFont = new WritableFont(WritableFont.createFont("新宋体"), 8,

WritableFont.NO_BOLD, false,

UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

} else {

dataFont = new WritableFont(WritableFont.createFont("新宋体"), 10,

WritableFont.NO_BOLD, false,

UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

}

dataFormat = new WritableCellFormat(dataFont);

if (null != customer && "settlement".equals(customer)) {

dataFormat.setWrap(true); // 设置自动换行

}

titleFont = new WritableFont(WritableFont.createFont("新宋体"), 14,

WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,

jxl.format.Colour.BLACK);

centerFormat = new WritableCellFormat(titleFont);

totleFont = new WritableFont(WritableFont.createFont("新宋体"), 10,

WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,

jxl.format.Colour.RED);

totleFormat = new WritableCellFormat(totleFont);

fontColorFormat = new WritableCellFormat(totleFont);

fontColorFormat.setAlignment(jxl.write.Alignment.LEFT); // 设置水平对齐方式

fontColorFormat.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式

centerFormat.setAlignment(jxl.write.Alignment.CENTRE);

centerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

totleFormat.setAlignment(jxl.write.Alignment.CENTRE);

totleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

headerFormat.setAlignment(jxl.write.Alignment.LEFT);

headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

dataFormat.setAlignment(jxl.write.Alignment.LEFT);

dataFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

WritableSheet wsheet = wbook.createSheet(title, 0); // sheet名称

wsheet.getSettings().setVerticalFreeze(2);// 冻结

if (null != customer && "customer".equals(customer)) {

wsheet.getSettings().setVerticalFreeze(3);// 冻结

wsheet.mergeCells(4, 1, 7, 1);// 合并单元格

wsheet.mergeCells(8, 1, 10, 1);// 合并单元格

wsheet.mergeCells(11, 1, 14, 1);// 合并单元格

wsheet.mergeCells(15, 1, 27, 1);// 合并单元格

wsheet.addCell(new Label(4, 1, "进库", headerFormat));// 设置 标题格式

wsheet.addCell(new Label(8, 1, "库存", headerFormat));// 设置 标题格式

wsheet.addCell(new Label(11, 1, "出库", headerFormat));// 设置 标题格式

}

if (null != customer && "settlement".equals(customer)) {

wsheet.getSettings().setVerticalFreeze(4);// 冻结

}

wsheet.mergeCells(0, 0, n, 0);// 合并单元格

wsheet.setRowView(0, 500); // 设置行的高度

for (int i = 0; i <= n; i++) {

wsheet.setColumnView(i, 18);// 设置列的宽度

}

wsheet.addCell(new Label(0, 0, title, centerFormat));// 设置 标题格式

wf = new WritableCellFormat(dataFormat);

wf.setBorder(Border.ALL, BorderLineStyle.THIN); // 添加边框

wj = new WritableCellFormat(totleFormat);

wj.setBorder(Border.ALL, BorderLineStyle.THIN); // 添加边框

wl = new WritableCellFormat(fontColorFormat);

return wsheet;

} catch (Exception e1) {

e1.printStackTrace();

return null;

}

}

/**

* 客户对账单导出

* 

* @param mtWmsFinanceDto

* @param res

* @param path

*/

public void customerStatements_export(MtWmsFinanceDto mtWmsFinanceDto,

HttpServletResponse res, String path, SysUser sysUser) {

try {

List<MtWmsFinanceDto> mtWmsFinanceDtolist = mtWmsFinanceService

.findAllByCustomer(mtWmsFinanceDto);

OutputStream os = res.getOutputStream();

WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件

WritableSheet wsheet = makeExcel(wbook, res, new String(pm.get(

"setStatement").getBytes("8859_1"), "utf-8"), 27,

new String(pm.get("setStatement").getBytes("8859_1"),

"utf-8"), "customer");

wsheet.addCell(new Label(0, 2, new String(pm.get("intoGoodsNo")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(1, 2, new String(pm.get("customerName")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(2, 2, new String(pm.get("memo6").getBytes(

"8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(3, 2, new String(pm.get("googsName")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(4, 2, new String(pm.get("date").getBytes(

"8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(5, 2, new String(pm.get("cube").getBytes(

"8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(6, 2, new String(pm.get("quantity")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(7, 2, new String(pm.get("weight")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(8, 2, new String(pm.get("cube").getBytes(

"8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(9, 2, new String(pm.get("quantity")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(10, 2, new String(pm.get("weight")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(11, 2, new String(pm.get("date").getBytes(

"8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(12, 2, new String(pm.get("cube").getBytes(

"8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(13, 2, new String(pm.get("quantity")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(14, 2, new String(pm.get("weight")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(15, 2, new String(pm.get("storageMethods")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(16, 2, new String(pm.get("goodsPrice")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(17, 2, new String(pm.get("billingDays")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(18, 2, new String(pm.get("storageFeeCost")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(19, 2, new String(pm.get(

"storageFeeUncost").getBytes("8859_1"), "utf-8"),

headerFormat));

wsheet.addCell(new Label(20, 2, new String(pm

.get("intoLibraryCost").getBytes("8859_1"), "utf-8"),

headerFormat));

wsheet.addCell(new Label(21, 2, new String(pm.get(

"intoLibraryUncost").getBytes("8859_1"), "utf-8"),

headerFormat));

wsheet.addCell(new Label(22, 2, new String(pm.get("outLibraryCost")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(23, 2, new String(pm.get(

"outLibraryUncost").getBytes("8859_1"), "utf-8"),

headerFormat));

wsheet.addCell(new Label(24, 2, new String(pm.get("otherCost")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(25, 2, new String(pm.get("otherUncost")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(26, 2, new String(pm.get("totleFee")

.getBytes("8859_1"), "utf-8"), headerFormat));

wsheet.addCell(new Label(27, 2, new String(pm.get("shouldFee")

.getBytes("8859_1"), "utf-8"), headerFormat));

if (mtWmsFinanceDtolist != null && mtWmsFinanceDtolist.size() > 0) {

for (int i = 1; i < mtWmsFinanceDtolist.size() + 1; i++) {

MtWmsFinanceDto financeDto = (MtWmsFinanceDto) mtWmsFinanceDtolist

.get(i - 1);

wsheet.setRowView(i + 1, 300); // 设置行的高度

wsheet.addCell(new Label(0, i + 2, null != financeDto

.getGoodsNo() ? financeDto.getGoodsNo() : "", wf));

wsheet.addCell(new Label(1, i + 2, null != financeDto

.getCustomerName() ? financeDto.getCustomerName()

: "", wf));

wsheet.addCell(new Label(2, i + 2, null != financeDto

.getMemo6() ? financeDto.getMemo6() : "", wf));

wsheet.addCell(new Label(3, i + 2, null != financeDto

.getGoodsName() ? financeDto.getGoodsName() : "",

wf));

wsheet

.addCell(new Label(4, i + 2, null != financeDto

.getIntoDate() ? financeDto.getIntoDate()

: "", wf));

wsheet.addCell(new Label(5, i + 2, null != financeDto

.getIntoGoodsVolume() ? financeDto

.getIntoGoodsVolume()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(6, i + 2, null != financeDto

.getIntoGoodsNumber() ? financeDto

.getIntoGoodsNumber()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(7, i + 2, null != financeDto

.getIntoGoodsGrossWeight() ? financeDto

.getIntoGoodsGrossWeight()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(8, i + 2, null != financeDto

.getFreeGoodsVolume() ? financeDto

.getFreeGoodsVolume()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(9, i + 2, null != financeDto

.getFreeGoodsNumber() ? financeDto

.getFreeGoodsNumber()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(10, i + 2, null != financeDto

.getFreeGoodsGrossWeight() ? financeDto

.getFreeGoodsGrossWeight()

+ "" : "", wf));

wsheet.addCell(new Label(11, i + 2, null != financeDto

.getOutDate() ? financeDto.getOutDate() : "", wf));

wsheet.addCell(new Label(12, i + 2, null != financeDto

.getLastGoodsVolume() ? financeDto

.getLastGoodsVolume()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(13, i + 2, null != financeDto

.getLastGoodsNumber() ? financeDto

.getLastGoodsNumber()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(14, i + 2, null != financeDto

.getLastGoodsGrossWeight() ? financeDto

.getLastGoodsGrossWeight()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(15, i + 2, null != financeDto

.getDepotFashion() ? financeDto.getDepotFashion()

: "", wf));

wsheet.addCell(new Label(16, i + 2, null != financeDto

.getGoodsPrice() ? financeDto.getGoodsPrice() + ""

: 0 + "", wf));

wsheet.addCell(new Label(17, i + 2, null != financeDto

.getBillingDays() ? financeDto.getBillingDays()

+ "" : 0 + "", wf));

wsheet.addCell(new Label(18, i + 2, null != financeDto

.getStorageFee() ? new BigDecimal(financeDto

.getStorageFee()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(19, i + 2, null != financeDto

.getStorageUnfee() ? new BigDecimal(financeDto

.getStorageUnfee()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(20, i + 2, null != financeDto

.getIntoLibrary() ? new BigDecimal(financeDto

.getIntoLibrary()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(21, i + 2, null != financeDto

.getIntoUnlibrary() ? new BigDecimal(financeDto

.getIntoUnlibrary()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(22, i + 2, null != financeDto

.getOutLibrary() ? new BigDecimal(financeDto

.getOutLibrary()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(23, i + 2, null != financeDto

.getOutUnlibrary() ? new BigDecimal(financeDto

.getOutUnlibrary()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(24, i + 2, null != financeDto

.getLastAnchor() ? new BigDecimal(financeDto

.getLastAnchor()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(25, i + 2, null != financeDto

.getLastUnanchor() ? new BigDecimal(financeDto

.getLastUnanchor()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(26, i + 2, null != financeDto

.getTotleFee() ? new BigDecimal(financeDto

.getTotleFee()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

wsheet.addCell(new Label(27, i + 2, null != financeDto

.getShouldFee() ? new BigDecimal(financeDto

.getShouldFee()).setScale(2,

BigDecimal.ROUND_HALF_UP).doubleValue()

+ "" : "0", wf));

}

}

wbook.write();

wbook.close();

os.close();

res.flushBuffer();

} catch (Exception e) {

e.printStackTrace();

}

}