/**
* 初始化函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(
new CellRangeAddress(
titleRow.getRowNum(),
titleRow.getRowNum(),
titleRow.getRowNum(),
headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment =
this.sheet
.createDrawingPatriarch()
.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
public void format() {
wb = new XSSFWorkbook();
Map styles = createStyles(wb);
Sheet sheet = wb.createSheet("Loan Calculator");
sheet.setPrintGridlines(false);
sheet.setDisplayGridlines(false);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setColumnWidth(0, 768);
sheet.setColumnWidth(1, 768);
sheet.setColumnWidth(2, 2816);
sheet.setColumnWidth(3, 3584);
sheet.setColumnWidth(4, 3584);
sheet.setColumnWidth(5, 3584);
sheet.setColumnWidth(6, 3584);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(35F);
for (int i = 1; i <= 7; i++)
titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));
Cell titleCell = titleRow.getCell(2);
titleCell.setCellValue("Simple");
}
// =================================================================================================================================
private int exportFrontEquipListReport_header(
List<FrontEquipListReport_subtype> subtypes, int rownum, XSSFWorkbook wb, Sheet sheet) {
CellStyle style = getHeaderStyle(wb, (short) 11);
CellStyle style2 = getHeaderStyle(wb, (short) 10);
// 第一行列标题
Row row_subtype = sheet.createRow(rownum++);
Cell cell_subtype_0 = row_subtype.createCell(0);
cell_subtype_0.setCellValue("");
cell_subtype_0.setCellStyle(style);
sheet.setColumnWidth(0, 700);
Cell cell_subtype_1 = row_subtype.createCell(1);
cell_subtype_1.setCellValue("");
cell_subtype_1.setCellStyle(style);
sheet.setColumnWidth(1, 1800);
Cell cell_subtype_2 = row_subtype.createCell(2);
cell_subtype_2.setCellValue("");
cell_subtype_2.setCellStyle(style);
sheet.setColumnWidth(2, 4800);
// 第二行列标题
Row row_prod = sheet.createRow(rownum++);
Cell cell_prod_0 = row_prod.createCell(0);
cell_prod_0.setCellValue("序号");
cell_prod_0.setCellStyle(style2);
Cell cell_prod_1 = row_prod.createCell(1);
cell_prod_1.setCellValue("点位编号");
cell_prod_1.setCellStyle(style2);
Cell cell_prod_2 = row_prod.createCell(2);
cell_prod_2.setCellValue("点位名称");
cell_prod_2.setCellStyle(style2);
int cell_num = 3;
for (FrontEquipListReport_subtype subtype : subtypes) {
int start_cell_num = cell_num;
Cell cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellValue(subtype.getSubtype_name());
cell_subtype.setCellStyle(style);
for (FrontEquipListReport_prod prod : subtype.getProds()) {
if (start_cell_num != cell_num) {
cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellStyle(style);
}
Cell cell_prod = row_prod.createCell(cell_num);
cell_prod.setCellValue(prod.getProd_name() + "(" + prod.getProd_style() + ")");
cell_prod.setCellStyle(style2);
cell_num++;
}
// 对子类型进行横向的单元格合并
sheet.addMergedRegion(
new CellRangeAddress(1, (short) 1, start_cell_num, (short) cell_num - 1));
}
return rownum;
}
public static File writerFile(String[] title, List<String[]> content, String filePath)
throws IOException {
checkDir(filePath);
File f = new File(filePath);
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
wb = new SXSSFWorkbook();
setStyle(wb);
Sheet sheet = wb.createSheet("sheet1");
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
int tCount = title.length;
for (int i = 0; i < tCount; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(title[i]);
sheet.setColumnWidth(i, 5000);
}
int rnum = 1;
for (String[] c : content) {
Row r = sheet.createRow(rnum);
for (int i = 0; i < c.length; i++) {
Cell cell = r.createCell(i);
cell.setCellStyle(contentStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
sheet.setColumnWidth(i, 5000);
String v = c[i];
if (v == null) {
v = "";
}
cell.setCellValue(v);
}
rnum++;
}
wb.write(out);
out.flush();
wb.close();
out.close();
return f;
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
public static void copySheet(Sheet sheet, Sheet newSheet) {
int maxCol = 0;
for (int row = 0; row <= sheet.getLastRowNum(); row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = newSheet.getRow(row);
if (newRow == null) newRow = newSheet.createRow(row);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
for (int col = 0; col < oldRow.getLastCellNum(); col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col);
if (newCell == null) newCell = newRow.createCell(col);
copyCell(oldCell, newCell, true);
}
}
for (int col = 0; col <= maxCol; col++) {
if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
}
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cra = sheet.getMergedRegion(i);
newSheet.addMergedRegion(cra);
}
}
/**
* Main method for writing output of calculation
*
* @param outputFile path to output file
* @param finalList list with sorted containers and items
* @param sheet sheet to create
* @param lastRow last row for items of each container
*/
public void writeOutput(String outputFile) {
XSSFWorkbook outputBook = new XSSFWorkbook();
outputSheet = outputBook.createSheet();
outputSheet.setColumnWidth(0, 1300);
Row headingsRow = outputSheet.createRow(0);
setHeadings(headingsRow);
setValues();
writeToFile(outputFile, outputBook);
}
/**
* @param wb
* @param reviewerSheetName
*/
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
sheet.setColumnWidth(i, COL_WIDTH * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
}
}
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < HEADER_TITLES.length; i++) {
sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(HEADER_TITLES[i]);
}
}
public static void copyBlock(
Sheet sheet,
int startRow,
int startCol,
int endRow,
int endCol,
boolean copyStyle,
int rowOffset,
int colOffset,
List<CellRangeAddress> mergedRegions) {
for (int row = startRow; row <= endRow; row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = sheet.getRow(row + rowOffset);
if (newRow == null) newRow = sheet.createRow(row + rowOffset);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
if (logger.isDebugEnabled()) {
logger.debug("copy row {} to {}", row, row + rowOffset);
logger.debug("Set row height :{}", newRow.getHeightInPoints());
}
for (int col = startCol; col <= endCol; col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col + colOffset);
if (newCell == null) newCell = newRow.createCell(col + colOffset);
copyCell(oldCell, newCell, copyStyle, rowOffset, colOffset);
}
}
for (int col = startCol; col <= endCol; col++) {
if (sheet.getColumnWidth(col) >= 0)
sheet.setColumnWidth(col + colOffset, sheet.getColumnWidth(col));
}
if (mergedRegions != null) {
for (CellRangeAddress cra : mergedRegions) {
CellRangeAddress craNew =
new CellRangeAddress(
cra.getFirstRow() + rowOffset,
cra.getLastRow() + rowOffset,
cra.getFirstColumn() + colOffset,
cra.getLastColumn() + colOffset);
sheet.addMergedRegion(craNew);
}
}
}
@Override
public Result upload() {
Result result = new Result();
Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
for (Transaction transaction : savingsTransactions) {
try {
Gson gson = new Gson();
String payload = gson.toJson(transaction);
restClient.post(
"recurringdepositaccounts/"
+ transaction.getAccountId()
+ "/transactions?command="
+ transaction.getTransactionType(),
payload);
Cell statusCell =
savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
statusCell.setCellValue("Imported");
statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.LIGHT_GREEN));
} catch (Exception e) {
Cell savingsAccountIdCell =
savingsTransactionSheet
.getRow(transaction.getRowIndex())
.createCell(SAVINGS_ACCOUNT_NO_COL);
savingsAccountIdCell.setCellValue(transaction.getAccountId());
String message = parseStatus(e.getMessage());
Cell statusCell =
savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
statusCell.setCellValue(message);
statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.RED));
result.addError("Row = " + transaction.getRowIndex() + " ," + message);
}
}
savingsTransactionSheet.setColumnWidth(STATUS_COL, 15000);
writeString(STATUS_COL, savingsTransactionSheet.getRow(0), "Status");
return result;
}
@Test
public void test_poi() {
final int rowNum = 27;
final int colNum = 15;
HSSFWorkbook wb = null;
Sheet sheet = null;
String today = "2013/8/31";
String sign = "Month to date";
String[] titles = {
"",
"",
"",
"Chinapay eMail\r\n 商城总计",
"Japan Page\r\n 日本馆首页",
"Taiwan Page\r\n 台湾馆首页",
"USA Page\r\n 美国馆首页",
"Anhui Page\r\n 安徽馆首页",
"China Page\r\n 中国馆首页"
};
String[] colNames = {
"",
"Page View (PV)\r\n 浏览量",
"Unique Visitor (UV)\r\n 独立访客",
"Completed Orders\r\n 确认订单",
"Transaction Amount\r\n 交易金额",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量"
};
int n = 0;
int len = 1;
String fileName = "D:/日报.xls";
File f = new File(fileName);
ByteArrayOutputStream byteArrayOut = null;
BufferedImage bufferImg = null;
String[] jpgUrls = {
"http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
"http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
};
String[] https = {
"http://emall.chinapay.com/goods/37452/1010000109792.html",
"http://emall.chinapay.com/goods/44066/1010000119323.html",
"http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/37452/1010000120588.html",
"http://emall.chinapay.com/goods/37452/1010000107096.html"
};
URL url = null;
HSSFHyperlink link = null;
HSSFPatriarch patri = null;
HSSFClientAnchor anchor = null;
try {
if (!f.exists()) {
wb = new HSSFWorkbook();
} else {
FileInputStream in = new FileInputStream(fileName);
wb = new HSSFWorkbook(in);
}
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setLeftBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setWrapText(true);
sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));
// 设置列的宽度
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) 400);
Row row = null;
Cell cell = null;
for (int r = 0; r < rowNum; r++) {
row = sheet.createRow(r);
// 设置第1行当高度
if (r == 0) {
row.setHeightInPoints(30);
}
// 设置第2列以后的宽度(即列号>=2的列，列号从0开始)
if (r >= 2) {
sheet.setColumnWidth(r, 3020);
}
for (int c = 0; c < colNum; c++) {
cell = row.createCell(c);
cell.setCellStyle(style);
// 处理第一行
if (r == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));
if (c < 3) {
cell.setCellValue(titles[n++]);
} else {
if ((c & 1) == 1) {
System.out.println("c===" + c);
cell.setCellValue(titles[n++]);
}
}
}
// 处理第2~8行
if (r > 0 && r <= 8) {
if (c == 0) {
if (r < 8 && (r & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
System.err.println("row----->" + r + " len----->" + (len));
cell.setCellValue(colNames[len++]);
} else if (r > 8) {
System.out.println("len+++++++++>" + (len));
cell.setCellValue(colNames[len++]);
}
} else if (c == 1) {
cell.setCellValue((r & 1) == 1 ? today : sign);
System.err.println("r---->" + r);
} else if (c == 2) {
cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
} else {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
cell.setCellValue("26.55");
}
}
}
// 处理第8行以后的数据(不包括第8行)
if (r > 8) {
// 设置列高(图片的高度)
if (r % 3 == 0) {
sheet.getRow(r).setHeightInPoints(110);
}
if (c == 0) {
System.err.println("r---->" + r);
cell.setCellValue(colNames[r - 4]);
} else if (c == 1) {
cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));
} else if (c == 2) {
if (r % 9 == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));
if (r / 9 == 1) cell.setCellValue("当天");
else cell.setCellValue("当月");
cell.setCellStyle(style);
}
} else {
if (r % 3 == 0) {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
// 添加远程图片信息
url = new URL(jpgUrls[(c - 3) / 2]);
bufferImg = ImageIO.read(url.openStream());
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, "jpg", byteArrayOut);
patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
patri.createPicture(
anchor,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
bufferImg.flush();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// System.out.println(https[(c-3)/2]);
// link.setAddress("fetion/"+https[(c-3)/2]);
// cell.setHyperlink(link);
// link = (HSSFHyperlink) cell.getHyperlink();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// link.setAddress(https[(c-3)/2]);
// cell.setHyperlink(link);
}
} else {
if ((c & 1) == 0) {
link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
link.setAddress(https[(c - 3) / 2]);
cell.setHyperlink(link); // 设定单元格的链接
cell.setCellValue("图片超链接");
} else {
cell.setCellValue("Number");
}
}
}
}
}
}
// 备注
row = sheet.createRow(27);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
cell.setCellValue("* 销量排名不以销售金额计算，如相同销量者，则以PV量少者为优胜");
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("++++++++++++ EXCEl文件 success +++++++++++++");
}
public File exportNeolixFile(String start, String end) {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet1");
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 5000);
sheet.setColumnWidth(2, 5000);
ExcelGenerateHelper helper = ExcelGenerateHelper.getInstance(wb);
// 获取行索引
int rowIndex = 0;
int colIndex = 0;
// 表头
String[] titles = {"订单编号", "运单号", "发货备注"};
// 生成表头
helper.setRowIndex(rowIndex++);
helper.generateHeader(sheet, titles, 0, StringUtils.EMPTY, StringUtils.EMPTY);
// 循环生成数据行
String sql = "call get_NeolixMail('" + start + "','" + end + "')"; // SQL语句 //调用存储过程
logger.info(sql);
JdbcOperWithClose db = JdbcOperWithClose.getInstance(); // 创建DBHelper对象
try {
db.getPrepareStateDao(sql);
ResultSet rs = db.pst.executeQuery(); // 执行语句，得到结果集
while (rs.next()) {
String orderCode = rs.getString("orderCode");
String mailNum = rs.getString("mailnum");
String remark = rs.getString("remark");
colIndex = 0;
Row row = sheet.createRow(rowIndex++);
helper.createStringCell(row, colIndex++, orderCode); // 订单号
helper.createStringCell(row, colIndex++, mailNum); // 运单号
helper.createStringCell(row, colIndex++, remark); // 备注
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
db.close();
}
// 在application.conf中配置的路径
String path = Configuration.root().getString("export.path");
File file = new File(path);
file.mkdir(); // 判断文件夹是否存在，不存在就创建
FileOutputStream out = null;
String fileName = path + "neolix" + System.currentTimeMillis() + ".xls";
try {
out = new FileOutputStream(fileName);
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return new File(fileName);
}
public static void main(String[] args) throws Exception {
Workbook wb;
if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
else wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("Timesheet");
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
// title row
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("Weekly Timesheet");
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
// header row
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(40);
Cell headerCell;
for (int i = 0; i < titles.length; i++) {
headerCell = headerRow.createCell(i);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
}
int rownum = 2;
for (int i = 0; i < 10; i++) {
Row row = sheet.createRow(rownum++);
for (int j = 0; j < titles.length; j++) {
Cell cell = row.createCell(j);
if (j == 9) {
// the 10th cell contains sum over week days, e.g. SUM(C3:I3)
String ref = "C" + rownum + ":I" + rownum;
cell.setCellFormula("SUM(" + ref + ")");
cell.setCellStyle(styles.get("formula"));
} else if (j == 11) {
cell.setCellFormula("J" + rownum + "-K" + rownum);
cell.setCellStyle(styles.get("formula"));
} else {
cell.setCellStyle(styles.get("cell"));
}
}
}
// row with totals below
Row sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(35);
Cell cell;
cell = sumRow.createCell(0);
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellValue("Total Hrs:");
cell.setCellStyle(styles.get("formula"));
for (int j = 2; j < 12; j++) {
cell = sumRow.createCell(j);
String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");
if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
else cell.setCellStyle(styles.get("formula"));
}
rownum++;
sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(25);
cell = sumRow.createCell(0);
cell.setCellValue("Total Regular Hours");
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellFormula("L13");
cell.setCellStyle(styles.get("formula_2"));
sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(25);
cell = sumRow.createCell(0);
cell.setCellValue("Total Overtime Hours");
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellFormula("K13");
cell.setCellStyle(styles.get("formula_2"));
// set sample data
for (int i = 0; i < sample_data.length; i++) {
Row row = sheet.getRow(2 + i);
for (int j = 0; j < sample_data[i].length; j++) {
if (sample_data[i][j] == null) continue;
if (sample_data[i][j] instanceof String) {
row.getCell(j).setCellValue((String) sample_data[i][j]);
} else {
row.getCell(j).setCellValue((Double) sample_data[i][j]);
}
}
}
// finally set column widths, the width is measured in units of 1/256th of a character width
sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
for (int i = 2; i < 9; i++) {
sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
}
sheet.setColumnWidth(10, 10 * 256); // 10 characters wide
// Write the output to a file
String file = "timesheet.xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
}
private void setLayout(Sheet worksheet) {
Row rowHeader = worksheet.createRow(0);
rowHeader.setHeight((short) 500);
worksheet.setColumnWidth(OFFICE_NAME_COL, 4000);
worksheet.setColumnWidth(CLIENT_NAME_COL, 5000);
worksheet.setColumnWidth(SAVINGS_ACCOUNT_NO_COL, 3000);
worksheet.setColumnWidth(PRODUCT_COL, 4000);
worksheet.setColumnWidth(OPENING_BALANCE_COL, 4000);
worksheet.setColumnWidth(TRANSACTION_TYPE_COL, 3300);
worksheet.setColumnWidth(AMOUNT_COL, 4000);
worksheet.setColumnWidth(TRANSACTION_DATE_COL, 3000);
worksheet.setColumnWidth(PAYMENT_TYPE_COL, 3000);
worksheet.setColumnWidth(ACCOUNT_NO_COL, 3000);
worksheet.setColumnWidth(CHECK_NO_COL, 3000);
worksheet.setColumnWidth(RECEIPT_NO_COL, 3000);
worksheet.setColumnWidth(ROUTING_CODE_COL, 3000);
worksheet.setColumnWidth(BANK_NO_COL, 3000);
worksheet.setColumnWidth(LOOKUP_CLIENT_NAME_COL, 5000);
worksheet.setColumnWidth(LOOKUP_ACCOUNT_NO_COL, 3000);
worksheet.setColumnWidth(LOOKUP_PRODUCT_COL, 3000);
worksheet.setColumnWidth(LOOKUP_OPENING_BALANCE_COL, 3700);
worksheet.setColumnWidth(LOOKUP_SAVINGS_ACTIVATION_DATE_COL, 3500);
writeString(OFFICE_NAME_COL, rowHeader, "Office Name*");
writeString(CLIENT_NAME_COL, rowHeader, "Client Name*");
writeString(SAVINGS_ACCOUNT_NO_COL, rowHeader, "Account No.*");
writeString(PRODUCT_COL, rowHeader, "Product Name");
writeString(OPENING_BALANCE_COL, rowHeader, "Opening Balance");
writeString(TRANSACTION_TYPE_COL, rowHeader, "Transaction Type*");
writeString(AMOUNT_COL, rowHeader, "Amount*");
writeString(TRANSACTION_DATE_COL, rowHeader, "Date*");
writeString(PAYMENT_TYPE_COL, rowHeader, "Type*");
writeString(ACCOUNT_NO_COL, rowHeader, "Account No");
writeString(CHECK_NO_COL, rowHeader, "Check No");
writeString(RECEIPT_NO_COL, rowHeader, "Receipt No");
writeString(ROUTING_CODE_COL, rowHeader, "Routing Code");
writeString(BANK_NO_COL, rowHeader, "Bank No");
writeString(LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client");
writeString(LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account");
writeString(LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product");
writeString(LOOKUP_OPENING_BALANCE_COL, rowHeader, "Lookup Opening Balance");
writeString(LOOKUP_SAVINGS_ACTIVATION_DATE_COL, rowHeader, "Lookup Savings Activation Date");
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
Workbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
ThaicomUserDetail currentUser = (ThaicomUserDetail) model.get("currentUser");
Map<String, CellStyle> styles = createStyles(workbook);
Integer fiscalYear = (Integer) model.get("fiscalYear");
Activity activity = (Activity) model.get("activity");
Sheet sheet = workbook.createSheet("sheet1");
Integer oldYear = fiscalYear - 1;
Row firstRow = sheet.createRow(0);
Cell cell0 = firstRow.createCell(0);
cell0.setCellValue("วันที่พิมพ์รายงาน: " + printTimeFormat.format(new Date()));
firstRow = sheet.createRow(1);
Cell cell11 = firstRow.createCell(0);
cell11.setCellValue(
"ตรวจสอบแผนปฏิบัติการของ" + activity.getName() + " ประจำปีงบประมาณ " + fiscalYear);
cell11.setCellStyle(styles.get("title"));
/* Row subFirstRow = sheet.createRow(1);
Cell subCell11 = subFirstRow.createCell(0);
subCell11.setCellValue("ผู้จัดทำรายงาน " +
currentUser.getPerson().getFirstName() + " " + currentUser.getPerson().getLastName() +
" เวลาที่จัดทำรายงาน " + sdf.format(new Date()) + "น.");
Row secondRow = sheet.createRow(1);
Cell cell21 = secondRow.createCell(0);
cell21.setCellValue("หน่วยงาน " + currentUser.getWorkAt().getName());
cell21.setCellStyle(styles.get("title"));
*/
Row thirdRow = sheet.createRow(2);
Cell cell301 = thirdRow.createCell(0);
cell301.setCellValue("หน่วยงาน");
cell301.setCellStyle(styles.get("header"));
Cell cell302 = thirdRow.createCell(1);
cell302.setCellValue("เป้าหมาย");
cell302.setCellStyle(styles.get("header"));
Cell cell303 = thirdRow.createCell(2);
cell303.setCellValue("แผน/ผล");
cell303.setCellStyle(styles.get("header"));
Cell cell304 = thirdRow.createCell(3);
cell304.setCellValue("ตค." + oldYear.toString().substring(2, 4));
cell304.setCellStyle(styles.get("header"));
Cell cell305 = thirdRow.createCell(4);
cell305.setCellValue("พย." + oldYear.toString().substring(2, 4));
cell305.setCellStyle(styles.get("header"));
Cell cell306 = thirdRow.createCell(5);
cell306.setCellValue("ธค." + oldYear.toString().substring(2, 4));
cell306.setCellStyle(styles.get("header"));
Cell cell307 = thirdRow.createCell(6);
cell307.setCellValue("มค." + fiscalYear.toString().substring(2, 4));
cell307.setCellStyle(styles.get("header"));
Cell cell308 = thirdRow.createCell(7);
cell308.setCellValue("กพ." + fiscalYear.toString().substring(2, 4));
cell308.setCellStyle(styles.get("header"));
Cell cell309 = thirdRow.createCell(8);
cell309.setCellValue("มีค." + fiscalYear.toString().substring(2, 4));
cell309.setCellStyle(styles.get("header"));
Cell cell310 = thirdRow.createCell(9);
cell310.setCellValue("เมย." + fiscalYear.toString().substring(2, 4));
cell310.setCellStyle(styles.get("header"));
Cell cell311 = thirdRow.createCell(10);
cell311.setCellValue("พค." + fiscalYear.toString().substring(2, 4));
cell311.setCellStyle(styles.get("header"));
Cell cell312 = thirdRow.createCell(11);
cell312.setCellValue("มิย." + fiscalYear.toString().substring(2, 4));
cell312.setCellStyle(styles.get("header"));
Cell cell313 = thirdRow.createCell(12);
cell313.setCellValue("กค." + fiscalYear.toString().substring(2, 4));
cell313.setCellStyle(styles.get("header"));
Cell cell314 = thirdRow.createCell(13);
cell314.setCellValue("สค." + fiscalYear.toString().substring(2, 4));
cell314.setCellStyle(styles.get("header"));
Cell cell315 = thirdRow.createCell(14);
cell315.setCellValue("กย." + fiscalYear.toString().substring(2, 4));
cell315.setCellStyle(styles.get("header"));
Cell cell316 = thirdRow.createCell(15);
cell316.setCellValue("รวม");
cell316.setCellStyle(styles.get("header"));
Connection connection = dataSource.getConnection();
Organization searchOrg;
logger.debug(currentUser.getWorkAt().getId() + " : " + activity.getOwner().getId());
if (currentUser.getWorkAt().getId().equals(activity.getOwner().getId())
|| currentUser.getWorkAt().getId().equals(activity.getRegulator().getId())) {
searchOrg = new Organization();
searchOrg.setId(0L);
} else {
searchOrg = currentUser.getWorkAt();
if (searchOrg.getType() == OrganizationType.แผนก) {
searchOrg = searchOrg.getParent();
}
}
int i = 3;
int j = 0;
double s1 = 0.0;
double s2 = 0.0;
int orgId = 0;
PreparedStatement ps = null;
Statement st = connection.createStatement();
String sql =
"select t4.id, t4.name, '1' type, t3.id target_id, ' (เป้าหมาย '|| ltrim(to_char(t1.targetvalue,'999,999,999,999'))||' '||t3.name||')' target "
+ "from pln_activitytargetreport t1, pln_activitytarget t2, pln_targetunit t3, hrx_organization t4 "
+ "where t1.target_pln_acttarget_id = t2.id "
+ "and t1.owner_hrx_organization_id = t4.id "
+ "and t2.unit_pln_targetunit_id = t3.id "
+ "and t2.activity_pln_activity_id = "
+ activity.getId()
+ " and t4.parent_hrx_organization_id = "
+ searchOrg.getId()
+ " order by 1, 4 ";
ResultSet rs = st.executeQuery(sql);
logger.debug(sql);
/* "union all " +
"select t6.id, t6.name, '2' type, null, ' (จัดสรรเงิน '||nvl(ltrim(to_char(budgetallocated,'999,999,999,999')), '...')||' บาท)' " +
"from pln_activityperformance t5, hrx_organization t6 " +
"where t5.owner_hrx_organization_id = t6.id " +
"and t5.activity_pln_activity_id = " + activity.getId() +
" and t6.parent_hrx_organization_id = 0 " +
*/
while (rs.next()) {
Row rows = sheet.createRow(i);
Cell rsc10 = rows.createCell(0);
rsc10.setCellStyle(styles.get("cellleft"));
if (rs.getInt(1) != orgId) {
rsc10.setCellValue(rs.getString(2));
orgId = rs.getInt(1);
}
Cell rsc11 = rows.createCell(1);
rsc11.setCellStyle(styles.get("cellcenter"));
Cell rsc12 = rows.createCell(2);
rsc12.setCellValue("แผนการใช้เงิน");
rsc12.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Row rows2 = sheet.createRow(i + 1);
Cell rsc21 = rows2.createCell(0);
rsc21.setCellStyle(styles.get("cellleft"));
Cell rsc22 = rows2.createCell(1);
rsc22.setCellStyle(styles.get("cellcenter"));
Cell rsc23 = rows2.createCell(2);
rsc23.setCellValue("ผลการใช้เงิน");
rsc23.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows2.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Statement st2 = connection.createStatement();
ResultSet rs2;
String rs2SQL =
"select t1.fiscalmonth, nvl(sum(t1.budgetplan),0), nvl(sum(t1.budgetresult),0) "
+ "from pln_monthlybgtreport t1, pln_activityperformance t2, pln_activitytargetreport t3, pln_activitytarget t4 "
+ "where t1.performance_pln_actper_id = t2.id "
+ "and t2.id = t3.performance_pln_actper_id "
+ "and t3.target_pln_acttarget_id = t4.id "
+ "and t2.activity_pln_activity_id = "
+ activity.getId()
+ "and t4.unit_pln_targetunit_id = "
+ rs.getInt(4)
+ " and t2.owner_hrx_organization_id in (select id from hrx_organization connect by prior id = parent_hrx_organization_id start with id = "
+ rs.getInt(1)
+ ") "
+ " group by t1.fiscalmonth order by t1.fiscalmonth ";
rs2 = st2.executeQuery(rs2SQL);
j = 3;
s1 = 0.0;
s2 = 0.0;
while (rs2.next()) {
Cell rscj1 = rows.getCell(j);
rscj1.setCellValue(rs2.getDouble(2));
Cell rscj2 = rows2.getCell(j);
rscj2.setCellValue(rs2.getDouble(3));
s1 = s1 + rs2.getDouble(2);
s2 = s2 + rs2.getDouble(3);
j = j + 1;
}
rs2.close();
Cell rscs1 = rows.getCell(15);
rscs1.setCellValue(s1);
Cell rscs2 = rows2.getCell(15);
rscs2.setCellValue(s2);
Row rows3 = sheet.createRow(i + 2);
Cell rsc31 = rows3.createCell(0);
rsc31.setCellStyle(styles.get("cellleft"));
Cell rsc32 = rows3.createCell(1);
rsc32.setCellValue(rs.getString(5));
rsc32.setCellStyle(styles.get("cellcenter"));
Cell rsc33 = rows3.createCell(2);
rsc33.setCellValue("แผนงาน");
rsc33.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows3.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Row rows4 = sheet.createRow(i + 3);
Cell rsc41 = rows4.createCell(0);
rsc41.setCellStyle(styles.get("cellleft"));
Cell rsc42 = rows4.createCell(1);
rsc42.setCellStyle(styles.get("cellcenter"));
Cell rsc43 = rows4.createCell(2);
rsc43.setCellValue("ผลงาน");
rsc43.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows4.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Statement st3 = connection.createStatement();
ResultSet rs3;
String rs3SQL =
"select t1.fiscalmonth, sum(t1.activityplan), sum(t1.activityresult) "
+ "from pln_monthlyactreport t1, pln_activitytargetreport t2, pln_activitytarget t3, "
+ "(select id from hrx_organization "
+ "connect by prior id = parent_hrx_organization_id "
+ "start with id = "
+ rs.getInt(1)
+ ") t4 "
+ "where t1.report_pln_acttargetreport_id = t2.id "
+ "and t2.target_pln_acttarget_id = t3.id "
+ "and t2.owner_hrx_organization_id = t4.id "
+ "and t3.activity_pln_activity_id = "
+ activity.getId()
+ " and t3.unit_pln_targetunit_id = "
+ rs.getInt(4)
+ " group by t1.fiscalmonth order by t1.fiscalmonth ";
rs3 = st3.executeQuery(rs3SQL);
j = 3;
s1 = 0.0;
s2 = 0.0;
while (rs3.next()) {
Cell rscj1 = rows3.getCell(j);
rscj1.setCellValue(rs3.getDouble(2));
Cell rscj2 = rows4.getCell(j);
rscj2.setCellValue(rs3.getDouble(3));
s1 = s1 + rs3.getDouble(2);
s2 = s2 + rs3.getDouble(3);
j = j + 1;
}
rs3.close();
Cell rscs3 = rows3.getCell(15);
rscs3.setCellValue(s1);
Cell rscs4 = rows4.getCell(15);
rscs4.setCellValue(s2);
i = i + 4;
}
Statement st4 = connection.createStatement();
ResultSet rs4;
String rs4SQL =
"select t3.id target_id, ' (เป้าหมาย '|| ltrim(to_char(sum(t1.targetvalue),'999,999,999,999'))||' '||t3.name||')' target "
+ "from pln_activitytargetreport t1, pln_activitytarget t2, pln_targetunit t3, hrx_organization t4 "
+ "where t1.target_pln_acttarget_id = t2.id "
+ "and t1.owner_hrx_organization_id = t4.id "
+ "and t2.unit_pln_targetunit_id = t3.id "
+ "and t2.activity_pln_activity_id = "
+ activity.getId()
+ " and t4.parent_hrx_organization_id = "
+ searchOrg.getId()
+ "group by t3.id, t3.name ";
rs4 = st4.executeQuery(rs4SQL);
while (rs4.next()) {
Row rows5 = sheet.createRow(i);
Cell rsc50 = rows5.createCell(0);
if (rs4.isFirst()) {
rsc50.setCellValue("ยอดรวม");
rsc50.setCellStyle(styles.get("celltop"));
} else {
rsc50.setCellStyle(styles.get("cellleft"));
}
Cell rsc52 = rows5.createCell(2);
rsc52.setCellValue("แผนการใช้เงิน");
rsc52.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows5.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Row rows6 = sheet.createRow(i + 1);
Cell rsc61 = rows6.createCell(0);
rsc61.setCellStyle(styles.get("cellleft"));
Cell rsc62 = rows6.createCell(1);
rsc62.setCellStyle(styles.get("cellcenter"));
Cell rsc63 = rows6.createCell(2);
rsc63.setCellValue("ผลการใช้เงิน");
rsc63.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows6.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Statement st5 = connection.createStatement();
ResultSet rs5;
String rs5SQL =
"select t1.fiscalmonth, nvl(sum(t1.budgetplan),0), nvl(sum(t1.budgetresult),0) "
+ "from pln_monthlybgtreport t1, pln_activityperformance t2, pln_activitytargetreport t3, pln_activitytarget t4 "
+ "where t1.performance_pln_actper_id = t2.id "
+ "and t2.id = t3.performance_pln_actper_id "
+ "and t3.target_pln_acttarget_id = t4.id "
+ "and t2.activity_pln_activity_id = "
+ activity.getId()
+ "and t4.unit_pln_targetunit_id = "
+ rs4.getInt(1)
+ " group by t1.fiscalmonth order by t1.fiscalmonth ";
rs5 = st5.executeQuery(rs5SQL);
j = 3;
s1 = 0.0;
s2 = 0.0;
while (rs5.next()) {
Cell rscj1 = rows5.getCell(j);
rscj1.setCellValue(rs5.getDouble(2));
Cell rscj2 = rows6.getCell(j);
rscj2.setCellValue(rs5.getDouble(3));
s1 = s1 + rs5.getDouble(2);
s2 = s2 + rs5.getDouble(3);
j = j + 1;
}
rs5.close();
Cell rscs1 = rows5.getCell(15);
rscs1.setCellValue(s1);
Cell rscs2 = rows6.getCell(15);
rscs2.setCellValue(s2);
Row rows3 = sheet.createRow(i + 2);
Cell rsc51 = rows5.createCell(1);
rsc51.setCellStyle(styles.get("cellcenter"));
Cell rsc31 = rows3.createCell(0);
rsc31.setCellStyle(styles.get("cellleft"));
Cell rsc32 = rows3.createCell(1);
rsc32.setCellValue(rs4.getString(2));
rsc32.setCellStyle(styles.get("cellcenter"));
Cell rsc33 = rows3.createCell(2);
rsc33.setCellValue("แผนงาน");
rsc33.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows3.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Row rows4 = sheet.createRow(i + 3);
Cell rsc41 = rows4.createCell(0);
rsc41.setCellStyle(styles.get("cellleft"));
Cell rsc42 = rows4.createCell(1);
rsc42.setCellStyle(styles.get("cellcenter"));
Cell rsc43 = rows4.createCell(2);
rsc43.setCellValue("ผลงาน");
rsc43.setCellStyle(styles.get("cellcenter"));
for (j = 3; j < 16; j++) {
Cell rscj = rows4.createCell(j);
rscj.setCellStyle(styles.get("cellnumber2"));
}
Statement st3 = connection.createStatement();
ResultSet rs3;
String rs3SQL =
"select t1.fiscalmonth, sum(t1.activityplan), sum(t1.activityresult) "
+ "from pln_monthlyactreport t1, pln_activitytargetreport t2, pln_activitytarget t3 "
+ "where t1.report_pln_acttargetreport_id = t2.id "
+ "and t2.target_pln_acttarget_id = t3.id "
+ "and t3.activity_pln_activity_id = "
+ activity.getId()
+ " and t3.unit_pln_targetunit_id = "
+ rs4.getInt(1)
+ " group by t1.fiscalmonth order by t1.fiscalmonth ";
rs3 = st3.executeQuery(rs3SQL);
j = 3;
s1 = 0.0;
s2 = 0.0;
while (rs3.next()) {
Cell rscj1 = rows3.getCell(j);
rscj1.setCellValue(rs3.getDouble(2));
Cell rscj2 = rows4.getCell(j);
rscj2.setCellValue(rs3.getDouble(3));
s1 = s1 + rs3.getDouble(2);
s2 = s2 + rs3.getDouble(3);
j = j + 1;
}
rs3.close();
Cell rscs3 = rows3.getCell(15);
rscs3.setCellValue(s1);
Cell rscs4 = rows4.getCell(15);
rscs4.setCellValue(s2);
i = i + 4;
}
rs4.close();
Row rowE = sheet.createRow(i);
Cell re = rowE.createCell(0);
re.setCellStyle(styles.get("celltop"));
rs.close();
connection.close();
sheet.setColumnWidth(0, 15000);
sheet.setColumnWidth(1, 8000);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 3500);
sheet.setColumnWidth(4, 3500);
sheet.setColumnWidth(5, 3500);
sheet.setColumnWidth(6, 3500);
sheet.setColumnWidth(7, 3500);
sheet.setColumnWidth(8, 3500);
sheet.setColumnWidth(9, 3500);
sheet.setColumnWidth(10, 3500);
sheet.setColumnWidth(11, 3500);
sheet.setColumnWidth(12, 3500);
sheet.setColumnWidth(13, 3500);
sheet.setColumnWidth(14, 3500);
sheet.setColumnWidth(14, 3500);
sheet.setColumnWidth(15, 3500);
sheet.createFreezePane(3, 3);
}
private void widthSetup(Sheet oldSheet, Sheet newSheet) {
for (int i = 0; i < columnCount; i++)
newSheet.setColumnWidth(i, oldSheet.getColumnWidth(i + INDENT));
}
@Test
public void createex() {
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillPattern(CellStyle.NO_FILL);
Sheet sheet = wb.createSheet("数据日志");
Sheet sheet2 = wb.createSheet("second sheet");
String safeName =
WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
Sheet sheet3 = wb.createSheet(safeName);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(
HSSFHeader.font("Stencil-Normal", "Italic")
+ HSSFHeader.fontSize((short) 16)
+ "Right w/ Stencil-Normal Italic font and size 16");
sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
sheet.autoSizeColumn(2); // 调整第四列宽度
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(cellStyle);
// Or do it on one line.
row.createCell(1).setCellValue("表名");
row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
row.createCell(3).setCellValue("操作日期");
row.createCell(4).setCellValue("操作者");
List<Log> list = this.logList();
Row rw;
for (int i = 2; i < list.size(); i++) {
Log log = list.get(i);
rw = sheet.createRow((short) i);
rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
rw.createCell(1).setCellValue(log.getDataTable());
rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
rw.createCell(3)
.setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
rw.createCell(4).setCellValue(log.getAdminId());
}
String bp = this.getClass().getResource("/").toString();
try {
System.out.println(bp);
File f = new File("workbook.xls");
FileOutputStream fileOut = new FileOutputStream(f);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
