Java 操作Excel 分析 导出Execl
Java 操作Excel 分析 导出Execl
public String getExport(QcyOweOrder obj, String type, String path) throws Exception {
// TODO 导出
String url = null;
// 导出某个模版
if (obj != null && obj.getId() != null && type == null) {
obj = this.findById(obj);
url = path + "\\" +"有限公司_欠货明细单"+obj.getNo()+".xls";
//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
WritableWorkbook book = Workbook.createWorkbook(new File(url));
//创建一个可写入的工作表
//Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet sheet = book.createSheet("欠货明细单", 0);
// 画出表格样式,第一个参数是列的意思，第二个参数是设置列的宽度
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 15);
sheet.setColumnView(2, 15);
sheet.setColumnView(3, 15);
sheet.setColumnView(4, 20);
sheet.setColumnView(5, 15);
sheet.setColumnView(6, 15);
sheet.setColumnView(7, 15);
sheet.setColumnView(8, 15);
sheet.setColumnView(9, 15);
sheet.setColumnView(10, 15);
sheet.setColumnView(11, 15);
sheet.setColumnView(12, 15);
// 标题格式设置WritableFont字符串的格式化涉及到的是字体、粗细、字号等元素
WritableFont fontTableHeader = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD);
// 处代码使用了WritableCellFormat类，这个类非常重要，通过它可以指定单元格的各种 属性，后面的单元格格式化中会有更多描述。
WritableCellFormat formatTableHeader = new WritableCellFormat();
formatTableHeader.setFont(fontTableHeader);
formatTableHeader.setAlignment(jxl.format.Alignment.CENTRE);
formatTableHeader.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//处使用了Label类的构造子，指定了字串被赋予那种格式第一列第一行
Label labelTable = new Label(0, 0, "有限公司", formatTableHeader);//0列0行设置文字
Label labelTable1 = new Label(0, 1, "欠 货 明 细 单", formatTableHeader);//0列1行设置的内容
sheet.mergeCells(0, 0, 13, 0);//合并功能（开始列，开始行，结束列，结束行）
sheet.mergeCells(0, 1, 13, 1);
sheet.addCell(labelTable);
sheet.addCell(labelTable1);
// 表头格式设置
WritableFont fontColumnHeader = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
WritableCellFormat formatColumnHeader = new WritableCellFormat();
formatColumnHeader.setFont(fontColumnHeader);
formatColumnHeader.setAlignment(jxl.format.Alignment.CENTRE);
formatColumnHeader.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//设置列的标题
Label labelColumn0 = new Label(0, 2, "客户", formatColumnHeader);
Label labelColumn1 = new Label(1, 2, "开单日期", formatColumnHeader);
Label labelColumn2 = new Label(2, 2, "送货地址", formatColumnHeader);
Label labelColumn3 = new Label(3, 2, "订单编号", formatColumnHeader);
Label labelColumn4 = new Label(4, 2, "存货名称", formatColumnHeader);
Label labelColumn5 = new Label(5, 2, "主要单位", formatColumnHeader);
Label labelColumn6 = new Label(6, 2, "订货数量", formatColumnHeader);
Label labelColumn7 = new Label(7, 2, "审核数量", formatColumnHeader);
Label labelColumn8 = new Label(8, 2, "欠货数量", formatColumnHeader);
Label labelColumn9 = new Label(9, 2, "单价", formatColumnHeader);
Label labelColumn10 = new Label(10, 2, "金额", formatColumnHeader);
Label labelColumn11 = new Label(11, 2, "本次出货量", formatColumnHeader);
Label labelColumn12 = new Label(12, 2, "累计出货量", formatColumnHeader);
sheet.addCell(labelColumn0);
sheet.addCell(labelColumn1);
sheet.addCell(labelColumn2);
sheet.addCell(labelColumn3);
sheet.addCell(labelColumn4);
sheet.addCell(labelColumn5);
sheet.addCell(labelColumn6);
sheet.addCell(labelColumn7);
sheet.addCell(labelColumn8);
sheet.addCell(labelColumn9);
sheet.addCell(labelColumn10);
sheet.addCell(labelColumn11);
sheet.addCell(labelColumn12);
// 内容格式设置
WritableFont fontContentHeader = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
WritableCellFormat formatContentHeader = new WritableCellFormat();
formatContentHeader.setFont(fontContentHeader);
formatContentHeader.setAlignment(jxl.format.Alignment.CENTRE);
formatContentHeader.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// 遍历输出list
int totalAboveRow = 3;// 上部高度
float totalsum = 0;
List<Object[]> list = (List<Object[]>) this.findAllRBpdf(obj);
Label itemName = new Label(0, totalAboveRow, obj.getRequestBranchName(), formatContentHeader);//客户信息
sheet.addCell(itemName);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Label date = new Label(1, totalAboveRow, sdf.format(obj.getSalesDate()), formatContentHeader);//开单时间
sheet.addCell(date);
Label address = new Label(2, totalAboveRow, "", formatContentHeader);// 送货地址
sheet.addCell(address);
Label bomLable9 = null;
Label bomLable11 = null;
BdSecondaryUnit secoUnit =null;
for (int i = 0; i < list.size(); i++) {
Double price =0.0;
String priceStr ="";
String orderNumber ="";
String auditNumber ="";
String oweNumber ="";
String saleNumber ="";
//累计出货
String totalGoOutStr ="";
if(list.get(i)[9]!=null){
secoUnit = (BdSecondaryUnit) this.getQcyOweOrderDAO().getObject("from BdSecondaryUnit where product.id=" +list.get(i)[9]);
}
if (null != list.get(i)[4] && null != list.get(i)[10]&& null != list.get(i)[6]) {
price = Double.parseDouble(list.get(i)[4].toString())/Double.parseDouble(list.get(i)[10].toString())*Double.parseDouble( list.get(i)[6].toString()) ;
priceStr = Tool.keep4DecimalRounding1ToString(price);
}
totalsum += price;
//订货量
if (null != list.get(i)[3]&&null != list.get(i)[10]&& Double.parseDouble(list.get(i)[3].toString())>=Double.parseDouble(list.get(i)[10].toString())) {
orderNumber= (int) (Double.parseDouble(list.get(i)[3].toString())/Double.parseDouble( list.get(i)[10].toString()))+ list.get(i)[2].toString();
}
if (null != list.get(i)[3]&&null != list.get(i)[10]&& null != secoUnit&&secoUnit.getSecondary2Basic()!=null &&
Double.parseDouble(list.get(i)[3].toString()) % Double.parseDouble(list.get(i)[10].toString()) != 0) {
orderNumber=(orderNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[3].toString()) - (int) (Double.parseDouble(list.get(i)[3].toString())/ Double.parseDouble(list.get(i)[10].toString()))
* Double.parseDouble(list.get(i)[10].toString()))
/ secoUnit.getSecondary2Basic()) + secoUnit.getUnit().getName());
}else {
if (null != list.get(i)[3]&& null != list.get(i)[10]&& Double.parseDouble(list.get(i)[3].toString()) %Double.parseDouble(list.get(i)[10].toString())> 0&&null!= list.get(i)[11])
orderNumber=(orderNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[3].toString()) - (int) (Double.parseDouble(list.get(i)[3].toString()) / Double.parseDouble(list.get(i)[10].toString())) * Double.parseDouble(list.get(i)[10].toString())))
+ list.get(i)[11].toString());
}
//本次出货量
if (null != list.get(i)[4]&&null != list.get(i)[10]&& Double.parseDouble(list.get(i)[4].toString())>=Double.parseDouble(list.get(i)[10].toString())) {
saleNumber= (int) (Double.parseDouble(list.get(i)[4].toString())/Double.parseDouble( list.get(i)[10].toString()))+ list.get(i)[2].toString();
}
if (null != list.get(i)[4]&&null != list.get(i)[10]&& null != secoUnit&&secoUnit.getSecondary2Basic()!=null &&
Double.parseDouble(list.get(i)[4].toString()) % Double.parseDouble(list.get(i)[10].toString()) != 0) {
saleNumber=(saleNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[4].toString()) - (int) (Double.parseDouble(list.get(i)[4].toString())/ Double.parseDouble(list.get(i)[10].toString()))
* Double.parseDouble(list.get(i)[10].toString()))
/ secoUnit.getSecondary2Basic()) + secoUnit.getUnit().getName());
}else {
if (null != list.get(i)[4]&& null != list.get(i)[10]&& Double.parseDouble(list.get(i)[4].toString()) %Double.parseDouble(list.get(i)[10].toString())> 0&&null!= list.get(i)[11])
saleNumber=(saleNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[4].toString()) - (int) (Double.parseDouble(list.get(i)[4].toString()) / Double.parseDouble(list.get(i)[10].toString())) * Double.parseDouble(list.get(i)[10].toString())))
+ list.get(i)[11].toString());
}
//审核量
if (null != list.get(i)[8]&&null != list.get(i)[10]&& Double.parseDouble(list.get(i)[8].toString())>=Double.parseDouble(list.get(i)[10].toString())) {
auditNumber= (int) (Double.parseDouble(list.get(i)[8].toString())/Double.parseDouble( list.get(i)[10].toString()))+ list.get(i)[2].toString();
}
if (null != list.get(i)[8]&&null != list.get(i)[10]&& null != secoUnit&&secoUnit.getSecondary2Basic()!=null &&
Double.parseDouble(list.get(i)[8].toString()) % Double.parseDouble(list.get(i)[10].toString()) != 0) {
auditNumber=(auditNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[8].toString()) - (int) (Double.parseDouble(list.get(i)[8].toString())/ Double.parseDouble(list.get(i)[10].toString()))
* Double.parseDouble(list.get(i)[10].toString()))
/ secoUnit.getSecondary2Basic()) + secoUnit.getUnit().getName());
}else {
if (null != list.get(i)[8]&& null != list.get(i)[10]&& Double.parseDouble(list.get(i)[8].toString()) %Double.parseDouble(list.get(i)[10].toString())> 0&&null!= list.get(i)[11])
auditNumber=(auditNumber +Tool.keep2DecimalRounding( (Double.parseDouble(list.get(i)[8].toString()) - (int) (Double.parseDouble(list.get(i)[8].toString()) / Double.parseDouble(list.get(i)[10].toString())) * Double.parseDouble(list.get(i)[10].toString())))
+ list.get(i)[11].toString());
}
//欠货量
if (null != list.get(i)[5]&&null != list.get(i)[10]&& Double.parseDouble(list.get(i)[5].toString())>=Double.parseDouble(list.get(i)[10].toString())) {
oweNumber= (int) (Double.parseDouble(list.get(i)[5].toString())/Double.parseDouble( list.get(i)[10].toString()))+ list.get(i)[2].toString();
}
if (null != list.get(i)[5]&&null != list.get(i)[10]&& null != secoUnit&&secoUnit.getSecondary2Basic()!=null &&
Double.parseDouble(list.get(i)[5].toString()) % Double.parseDouble(list.get(i)[10].toString()) != 0) {
oweNumber=(oweNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[5].toString()) - (int) (Double.parseDouble(list.get(i)[5].toString())/ Double.parseDouble(list.get(i)[10].toString()))
* Double.parseDouble(list.get(i)[10].toString()))
/ secoUnit.getSecondary2Basic()) + secoUnit.getUnit().getName());
}else {
if (null != list.get(i)[5]&& null != list.get(i)[10]&& Double.parseDouble(list.get(i)[5].toString()) %Double.parseDouble(list.get(i)[10].toString())> 0&&null!= list.get(i)[11])
oweNumber=(oweNumber + Tool.keep2DecimalRounding((Double.parseDouble(list.get(i)[5].toString()) - (int) (Double.parseDouble(list.get(i)[5].toString()) / Double.parseDouble(list.get(i)[10].toString())) * Double.parseDouble(list.get(i)[10].toString())))
+ list.get(i)[11].toString());
}
Double totalGoOut = 0.0d;
if(null != list.get(i)[4] && null != list.get(i)[5] && null != list.get(i)[8]){
totalGoOut =Double.parseDouble(list.get(i)[8].toString()) - Double.parseDouble(list.get(i)[5].toString())+Double.parseDouble(list.get(i)[4].toString());
//累计出货量
if (null != list.get(i)[10]&& totalGoOut>=Double.parseDouble(list.get(i)[10].toString())) {
totalGoOutStr= (int) (totalGoOut/Double.parseDouble( list.get(i)[10].toString()))+ list.get(i)[2].toString();
}
if (null != list.get(i)[10]&& null != secoUnit&&secoUnit.getSecondary2Basic()!=null && totalGoOut% Double.parseDouble(list.get(i)[10].toString()) != 0) {
totalGoOutStr=(totalGoOutStr + Tool.keep2DecimalRounding((totalGoOut- (int) (Double.parseDouble(list.get(i)[5].toString())/ Double.parseDouble(list.get(i)[10].toString()))
* Double.parseDouble(list.get(i)[10].toString()))
/ secoUnit.getSecondary2Basic()) + secoUnit.getUnit().getName());
}else {
if (null != list.get(i)[10]&&totalGoOut%Double.parseDouble(list.get(i)[10].toString())> 0&&null!= list.get(i)[11])
totalGoOutStr=(totalGoOutStr + Tool.keep2DecimalRounding((totalGoOut - (int) (totalGoOut/ Double.parseDouble(list.get(i)[10].toString())) * Double.parseDouble(list.get(i)[10].toString())))
+ list.get(i)[11].toString());
}
}
Label bomLable0 = new Label(3, totalAboveRow + i, "" + isNull(list.get(i)[0]), formatContentHeader);
Label bomLable1 = new Label(4, totalAboveRow + i, "" + isNull(list.get(i)[1]), formatContentHeader);
Label bomLable2 = new Label(5, totalAboveRow + i, "" + isNull(list.get(i)[2]), formatContentHeader);
Label bomLable3 = new Label(6, totalAboveRow + i, "" + isNull(orderNumber), formatContentHeader);//订货量
Label bomLable4 = new Label(7, totalAboveRow + i, "" + isNull(auditNumber), formatContentHeader);//审核量
Label bomLable5 = new Label(8, totalAboveRow + i, "" + isNull(oweNumber), formatContentHeader);//欠货量
Label bomLable6 = new Label(9, totalAboveRow + i, "" + isNull(list.get(i)[6]), formatContentHeader);
Label bomLable100 = new Label(10, totalAboveRow + i, "" + isNull(priceStr), formatContentHeader);
Label bomLable111 = new Label(11, totalAboveRow + i, "" + isNull(saleNumber), formatContentHeader);
Label bomLable120 = new Label(12, totalAboveRow + i, "" + isNull(totalGoOutStr), formatContentHeader);
bomLable9 = new Label(0, totalAboveRow + 1 + i, "合 计", formatColumnHeader);
bomLable11 = new Label(10, totalAboveRow + 1 + i, "" +Tool.keep4DecimalRounding1ToString( totalsum), formatColumnHeader);
sheet.addCell(bomLable0);
sheet.addCell(bomLable1);
sheet.addCell(bomLable2);
sheet.addCell(bomLable3);
sheet.addCell(bomLable4);
sheet.addCell(bomLable5);
sheet.addCell(bomLable6);
sheet.addCell(bomLable100);
sheet.addCell(bomLable111);
sheet.addCell(bomLable120);
}
sheet.addCell(bomLable9);
sheet.addCell(bomLable11);
if (book != null) {
book.write();
book.close();
}
}
return url;
}
具体格式如下：
评论暂时关闭