private void createLabel(WritableSheet sheet) throws WriteException {
WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
// Define the cell format
times = new WritableCellFormat(times10pt);
// Lets automatically wrap the cells
times.setWrap(true);
// create create a bold font with unterlines
WritableFont times10ptBoldUnderline =
new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE);
timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
// Lets automatically wrap the cells
timesBoldUnderline.setWrap(true);
CellView cv = new CellView();
cv.setFormat(times);
cv.setFormat(timesBoldUnderline);
// Write a few headers
addCaption(sheet, 0, 0, "Adı");
addCaption(sheet, 1, 0, "Miktar");
addCaption(sheet, 2, 0, "Birim");
addCaption(sheet, 3, 0, "Fiyat");
addCaption(sheet, 4, 0, "Etiketler");
}
public static void addColumn(
WritableSheet sheet, int startCol, int startRow, CellValue[] cellValues)
throws WriteException {
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setWrap(true);
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
NumberFormat towdps = new NumberFormat("#,##0");
WritableCellFormat towdpsFormat = new WritableCellFormat(towdps);
towdpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
NumberFormat towdps2 = new NumberFormat("#,##0.00");
WritableCellFormat towdpsFormat2 = new WritableCellFormat(towdps2);
towdpsFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
DateFormat customDateFormat = new DateFormat("dd MMM yyyy");
WritableCellFormat dateFormat = new WritableCellFormat(customDateFormat);
dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
for (int i = startRow; i < cellValues.length; i++) {
if (cellValues[i] != null && cellValues[i].getValue() != null) {
if (cellValues[i].getType().equals(CellDataType.STRING)) {
Label label =
new Label(startCol, i, String.valueOf(cellValues[i].getValue()), cellFormat);
sheet.addCell(label);
} else if (cellValues[i].getType().equals(CellDataType.INT)) {
Number number = new Number(startCol, i, (Integer) cellValues[i].getValue(), towdpsFormat);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.DOUBLE)) {
Number number = new Number(startCol, i, (Double) cellValues[i].getValue(), towdpsFormat);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.FLOAT)) {
Number number = new Number(startCol, i, (Float) cellValues[i].getValue(), towdpsFormat);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.DATE)) {
Date now = new Date(((Timestamp) cellValues[i].getValue()).getTime());
DateTime dateCell = new DateTime(startCol, i, now, dateFormat);
sheet.addCell(dateCell);
}
} else {
Label label = new Label(startCol, i, "", cellFormat);
sheet.addCell(label);
}
}
}
private void exportReport2Excel(
ExportMaterialReportBean bean,
ExportMaterialReportDTO results,
HttpServletRequest request,
HttpServletResponse response) {
try {
String outputFileName =
"/files/temp/BaoCaoNhapXuatTonNPL" + System.currentTimeMillis() + ".xls";
String reportTemplate =
request
.getSession()
.getServletContext()
.getRealPath("/files/export/ExportMaterialReport.xls");
String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName);
Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate));
WritableWorkbook workbook =
Workbook.createWorkbook(new File(export2FileName), templateWorkbook);
WritableSheet sheet = workbook.getSheet(0);
WritableFont normalFont =
new WritableFont(
WritableFont.TIMES,
12,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
normalFormat.setAlignment(Alignment.LEFT);
normalFormat.setWrap(true);
normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
WritableFont boldFont =
new WritableFont(
WritableFont.TIMES,
12,
WritableFont.BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat headerFormat = new WritableCellFormat(boldFont);
headerFormat.setAlignment(Alignment.CENTRE);
headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
headerFormat.setWrap(true);
headerFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
headerFormat.setBackground(jxl.format.Colour.GRAY_25);
WritableCellFormat boldFormat = new WritableCellFormat(boldFont);
boldFormat.setAlignment(Alignment.CENTRE);
boldFormat.setWrap(true);
boldFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
DecimalFormat decimalFormat = new DecimalFormat("###,###.##");
Date from = bean.getFromDate();
Label fromCell =
new Label(
1, 1, from != null ? DateUtils.date2String(from, "dd/MM/yyyy") : "", boldFormat);
sheet.addCell(fromCell);
Date to = bean.getToDate();
Label toCell =
new Label(1, 2, to != null ? DateUtils.date2String(to, "dd/MM/yyyy") : "", boldFormat);
sheet.addCell(toCell);
int startRow = 5;
List<ExportMaterialReportDetailDTO> initialValue = results.getInitialValue();
Map<String, Double> mapImportValue = results.getMapImportValue();
Map<String, Double> mapExportUtilDateValue = results.getMapExportUtilDateValue();
Map<String, Double> mapExportDuringDateValue = results.getMapExportDuringDateValue();
int index;
CellValue[] resValue;
Double iVal;
Double exportToVal;
Double importVal;
Double exportVal;
Double remainVal;
String key, origin;
for (ExportMaterialReportDetailDTO initVal : initialValue) {
origin = initVal.getOrigin() != null ? initVal.getOrigin().getOriginID().toString() : "";
key = initVal.getMaterial().getMaterialID() + "_" + origin;
exportToVal =
mapExportUtilDateValue.get(key) != null ? mapExportUtilDateValue.get(key) : 0d;
iVal =
initVal.getQuantity() != null ? initVal.getQuantity() - exportToVal : 0 - exportToVal;
importVal = mapImportValue.get(key) != null ? mapImportValue.get(key) : 0d;
exportVal =
mapExportDuringDateValue.get(key) != null ? mapExportDuringDateValue.get(key) : 0d;
remainVal = iVal + importVal - exportVal;
index = 0;
resValue = new CellValue[10];
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getOrigin() != null ? initVal.getOrigin().getName() : "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getMaterial() != null ? initVal.getMaterial().getName() : "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
StringUtils.isNotBlank(initVal.getCode()) ? initVal.getCode() : "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getMaterial() != null
? initVal.getMaterial().getUnit() != null
? initVal.getMaterial().getUnit().getName()
: ""
: "");
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(iVal));
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(importVal));
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(exportVal));
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(remainVal));
resValue[index++] = new CellValue(CellDataType.STRING, "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getImportDate() != null
? DateUtils.date2String(initVal.getImportDate(), "dd/MM/yyyy")
: "");
ExcelUtil.addRow(
sheet, startRow++, resValue, normalFormat, normalFormat, normalFormat, normalFormat);
}
workbook.write();
workbook.close();
response.sendRedirect(
request.getSession().getServletContext().getContextPath() + outputFileName);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
}
private void exportUsedMaterial2Excel(
SearchUsedMaterialBean bean,
SummaryUsedMaterialDTO result,
HttpServletRequest request,
HttpServletResponse response) {
try {
String outputFileName = "/files/temp/VatTuSuDung_" + System.currentTimeMillis() + ".xls";
String reportTemplate =
request.getSession().getServletContext().getRealPath("/files/export/UsedMaterial.xls");
String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName);
Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate));
WritableWorkbook workbook =
Workbook.createWorkbook(new File(export2FileName), templateWorkbook);
WritableSheet sheet = workbook.getSheet(0);
WritableFont normalFont =
new WritableFont(
WritableFont.TIMES,
12,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
normalFormat.setAlignment(Alignment.CENTRE);
normalFormat.setWrap(true);
normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
DecimalFormat decimalFormat = new DecimalFormat("###,###.##");
DecimalFormat decimalFormat2 = new DecimalFormat("###,###.####");
int startRow = 4;
int stt = 1;
List<UsedMaterialDTO> shareUsedMaterials = result.getShareUsedMaterials();
List<UsedMaterialDTO> usedProducts = result.getUsedProducts();
List<UsedMaterialDTO> usedMeasurementMaterials = result.getUsedMeasurementMaterials();
Double totalMainProductKg = 0d;
Double totalMainProductMet2 = 0d;
Integer counter = 0;
Double totalMet2 = 0d;
Double totalKg = 0d;
Double kgMet;
Double kgTan;
Double materialUsed;
if (usedProducts != null && usedProducts.size() > 0) {
for (UsedMaterialDTO usedProduct : usedProducts) {
totalKg = usedProduct.getTotalKgUsed();
totalMet2 = usedProduct.getTotalMUsed() * Integer.valueOf(usedProduct.getWidth()) / 1000;
counter++;
totalMainProductKg += totalKg;
totalMainProductMet2 += totalMet2;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedProduct.getProductName().getName(),
"Kg",
totalKg,
null,
null);
if (usedProduct.getUsedMaterialDTOs() != null
&& usedProduct.getUsedMaterialDTOs().size() > 0) {
for (UsedMaterialDTO usedMaterial : usedProduct.getUsedMaterialDTOs()) {
counter++;
materialUsed = usedMaterial.getTotalUsed();
kgMet = totalMet2 > 0 ? materialUsed / totalMet2 : null;
kgTan = totalKg > 0 ? materialUsed * 1000 / totalKg : null;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedMaterial.getMaterial().getName(),
usedMaterial.getMaterial().getUnit().getName(),
materialUsed,
kgMet,
kgTan);
}
}
}
}
if (usedMeasurementMaterials != null && usedMeasurementMaterials.size() > 0) {
for (UsedMaterialDTO usedMaterial : usedMeasurementMaterials) {
counter++;
materialUsed = usedMaterial.getTotalUsed();
kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null;
kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedMaterial.getMaterial().getName(),
usedMaterial.getMaterial().getUnit().getName(),
materialUsed,
kgMet,
kgTan);
}
}
if (shareUsedMaterials != null && shareUsedMaterials.size() > 0) {
for (UsedMaterialDTO usedMaterial : shareUsedMaterials) {
counter++;
materialUsed = usedMaterial.getTotalUsed();
kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null;
kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedMaterial.getMaterial().getName(),
usedMaterial.getMaterial().getUnit().getName(),
materialUsed,
kgMet,
kgTan);
}
}
workbook.write();
workbook.close();
response.sendRedirect(
request.getSession().getServletContext().getContextPath() + outputFileName);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
}
/**
* 业务逻辑实现 - 点击统计下载excel
*
* @param ws 工作表
* @param totals 待写入数据
* @throws WriteException
*/
@SuppressWarnings("unchecked")
private static void addLabelExcel(WritableSheet ws, List totals, List columnList)
throws WriteException {
int row = 0, column = 0;
// 设置Excel列中文标题
Iterator itColumnList = columnList.iterator();
while (itColumnList.hasNext()) {
DownloadAttribute columnTitle = (DownloadAttribute) itColumnList.next();
ws.addCell(new Label(column, row, columnTitle.getExcelName()));
column++;
}
if (totals != null && totals.size() != 0) {
Iterator itTotals = totals.iterator();
WritableFont detFont =
new WritableFont(
WritableFont.ARIAL,
10,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
NumberFormat pnf = new NumberFormat("0.00"); // 用于Price Number的格式
WritableCellFormat priceFormat = new WritableCellFormat(detFont, pnf);
NumberFormat nf = new NumberFormat("0"); // 用于Number的格式
WritableCellFormat numberFormat = new WritableCellFormat(detFont, nf);
WritableFont stringFont =
new WritableFont(
WritableFont.ARIAL,
10,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat stringFormat = new WritableCellFormat(stringFont);
WritableCellFormat stringWrapFormat = new WritableCellFormat(stringFont);
stringWrapFormat.setWrap(true);
row = 1;
while (itTotals.hasNext()) {
Object obj = itTotals.next();
Map subTotals = obj instanceof Map ? (HashMap) obj : ObjectUtil.transform(obj);
Iterator itColumnName = columnList.iterator();
column = 0;
while (itColumnName.hasNext()) {
DownloadAttribute columnInfo = (DownloadAttribute) itColumnName.next();
String columnType = columnInfo.getExcelType();
String columnName = columnInfo.getRowName();
Object columnValue = subTotals.get(columnName);
if (columnValue == null) {
column++;
continue;
}
String strValue = null;
if (columnValue instanceof Date) {
strValue = DateTimeUtil.getFormatDateTime((Date) columnValue);
} else {
strValue = columnValue.toString();
}
// 设置内容
if (columnType.equals("Label")) {
if (columnValue.toString().indexOf("\r\n") > 0) {
ws.addCell(new Label(column, row, strValue, stringWrapFormat));
} else {
ws.addCell(new Label(column, row, strValue, stringFormat));
}
}
if (columnType.equals("Number"))
ws.addCell(
new jxl.write.Number(column, row, Double.parseDouble(strValue), numberFormat));
if (columnType.equals("Price"))
ws.addCell(
new jxl.write.Number(column, row, Double.parseDouble(strValue), priceFormat));
column++;
}
row++;
}
}
}