/**
* 初始化函数
*
* @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.");
}
private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
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 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");
}
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;
}
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 Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
XSSFWorkbook wb = null;
try {
if (isXssf) {
XSSFWorkbook w = new XSSFWorkbook();
} else {
HSSFWorkbook w = new HSSFWorkbook();
}
wb = new XSSFWorkbook();
XSSFDataFormat format = wb.createDataFormat();
XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
XSSFDataFormat passwordFormat = wb.createDataFormat();
passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
Row r0 = sheet.createRow(0);
Cell cell = r0.createCell(0);
r0.setHeightInPoints(28);
cell.setCellValue(message + "备份记录");
Row r = sheet.createRow(1);
r.setHeightInPoints(25);
cell.setCellStyle(cellStyle);
// 输出标题
for (int i = 0; i < headers.size(); i++) {
Cell cell1 = r.createCell(i);
if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
else cell1.setCellStyle(cellStyle);
cell1.setCellValue(headers.get(i).getTitle());
}
Object obj = null;
// 输出用户资料信息
if (message.indexOf("用户资料 ") > 0) {
sheet.setColumnWidth(3, 32 * 150);
sheet.setColumnWidth(4, 32 * 110);
sheet.setColumnWidth(7, 32 * 120);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
copyDefaultCellStyle(null, cell2, cellStyle, 0);
if (getMethodName(headers.get(j)).equals("nabled"))
cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
else if (getMethodName(headers.get(j)).equals("password")) {
cell2.setCellStyle(passwordCellStyle);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
} else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 输出房间使用信息数据
else {
sheet.setColumnWidth(0, 32 * 80);
sheet.setColumnWidth(2, 32 * 100);
sheet.setColumnWidth(3, 32 * 190);
sheet.setColumnWidth(4, 32 * 190);
sheet.setColumnWidth(5, 32 * 190);
sheet.setColumnWidth(10, 32 * 130);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
if (j == 3 || j == 4 || j == 5) {
XSSFCellStyle cs3 = wb.createCellStyle();
cell2.setCellValue(new Date());
copyDefaultCellStyle(format, cell2, cs3, 1);
}
if (j == 10) {
XSSFCellStyle cs2 = wb.createCellStyle();
copyDefaultCellStyle(format, cell2, cs2, 2);
}
copyDefaultCellStyle(null, cell2, cellStyle, 0);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 设置行列的默认宽度和高度
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NoSuchMethodException e) {
e.printStackTrace();
logger.error(e);
}
return wb;
}
// Define A method for Creating Excel File
public String createExcel(
String titleString,
String sheetString,
String subTitleString,
List data,
String[] headerTitles,
String titleKey,
String filePath) {
String file = null;
Workbook wb;
int col = 0;
try {
// check Header Title
if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;
wb = (Workbook) new HSSFWorkbook();
// Hear we are getting whole property
List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetString);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight("Right Footer");
Footer footer = sheet.getFooter();
footer.setCenter("center footer");
footer.setLeft("left footer");
footer.setRight("right footer");
// Title Row....
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(titleString);
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
// Sub Title Row.....
// System.out.println("Sub Title String >>>>>>"+subTitleString);
Row headerRow = null;
if (subTitleString != "") {
Row subTitleRow = sheet.createRow(1);
subTitleRow.setHeightInPoints(18);
Cell subTitleCell = subTitleRow.createCell(0);
subTitleCell.setCellValue(subTitleString);
subTitleCell.setCellStyle(styles.get("subTitle"));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col - 1));
headerRow = sheet.createRow(2);
headerRow.setHeightInPoints(15);
//
} else {
headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(15);
}
Cell headerCell = null;
if (headerTitles != null) {
for (ConfigurationUtilBean cell : titleMap) {
int titleIndex = 0;
for (int i = 0; i < headerTitles.length; i++) {
if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
headerCell = headerRow.createCell(titleIndex);
headerCell.setCellValue(cell.getValue());
headerCell.setCellStyle(styles.get("header"));
}
titleIndex++;
}
}
}
Row dataRow = null;
Cell dataCell = null;
int rowIndex = 2;
/* List Iteration text */
try {
if (data != null && data.size() > 0) {
for (Iterator it = data.iterator(); it.hasNext(); ) {
Object[] obdata = (Object[]) it.next();
dataRow = sheet.createRow(rowIndex);
for (int cellIndex = 0; cellIndex < headerTitles.length; cellIndex++) {
dataCell = dataRow.createCell(cellIndex);
if (obdata[cellIndex] != null && !obdata[cellIndex].toString().equalsIgnoreCase("")) {
dataCell.setCellValue(obdata[cellIndex].toString());
} else {
dataCell.setCellValue("NA");
}
}
rowIndex++;
}
}
} catch (Exception e) {
// TODO: handle exception
}
for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
sheet.autoSizeColumn(titleIndex); // adjust width of the column
file =
filePath
+ File.separator
+ "OpportunityReportDetail_"
+ DateUtil.getCurrentDateIndianFormat()
+ (DateUtil.getCurrentTimeHourMin()).replaceAll(":", "-")
+ ".xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file;
}
// Define A method for Creating Excel File
public String createExcelformate(
String titleString,
String sheetString,
String[] headerTitles,
String titleKey,
String filePath) {
String file = null;
Workbook wb;
int col = 0;
try {
// check Header Title
if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;
wb = (Workbook) new HSSFWorkbook();
// Hear we are getting whole property
List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetString);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight("Right Footer");
Footer footer = sheet.getFooter();
footer.setCenter("center footer");
footer.setLeft("left footer");
footer.setRight("right footer");
// Title Row....
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(titleString);
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
//
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(15);
Cell headerCell = null;
if (headerTitles != null) {
for (ConfigurationUtilBean cell : titleMap) {
int titleIndex = 0;
for (int i = 0; i < headerTitles.length; i++) {
if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
headerCell = headerRow.createCell(titleIndex);
headerCell.setCellValue(cell.getValue());
headerCell.setCellStyle(styles.get("header"));
}
titleIndex++;
}
}
}
for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
sheet.autoSizeColumn(titleIndex); // adjust width of the column
file =
filePath
+ File.separator
+ "ContactReport"
+ DateUtil.getCurrentDateIndianFormat()
+ (DateUtil.getCurrentTime()).replaceAll(":", "-")
+ ".xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file;
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession ss = request.getSession();
Account ac = (Account) ss.getAttribute("ac");
int cId = Integer.parseInt((Long) ss.getAttribute("cId") + "");
Course c = Course.getCourseByID(cId);
Workbook wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("scoresheet");
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("Score sheet of " + c.getName() + " course");
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));
List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore");
int rownum = 2;
int cellcount = 1;
Row sumRow = sheet.createRow(rownum);
sumRow.setHeightInPoints(55);
Cell cell;
cell = sumRow.createCell(0);
cell.setCellValue("Student name");
cell.setCellStyle(styles.get("header"));
int countback = listStudentScore.get(0).getListStudentScore().size();
int maxScore = 0;
for (int i = countback - 1; i >= 0; i--) {
cell = sumRow.createCell(cellcount);
UserScore u = listStudentScore.get(0).getListStudentScore().get(i);
cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")");
cell.setCellStyle(styles.get("header"));
cellcount++;
maxScore += u.getFull_mark();
}
cell = sumRow.createCell(cellcount);
cell.setCellValue("Total (" + maxScore + ")");
cell.setCellStyle(styles.get("header"));
rownum++;
for (Account account : listStudentScore) {
sumRow = sheet.createRow(rownum);
sumRow.setHeightInPoints(35);
cell = sumRow.createCell(0);
cell.setCellValue(account.getFirstname() + " " + account.getLastname());
int j = 1;
for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) {
UserScore usc = (UserScore) account.getListStudentScore().get(i);
cell = sumRow.createCell(j);
Assignment a = null;
if (usc.getAss_type().equalsIgnoreCase("web")) {
a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id());
String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a);
if (status.equalsIgnoreCase("ontime")
|| status.equalsIgnoreCase("hurryup")
|| status.equalsIgnoreCase("late")) {
cell.setCellValue(usc.getStof().getScore());
} else {
status = Assignment.calculateTime(a);
if (status.equalsIgnoreCase("miss")) {
cell.setCellValue(usc.getStof().getScore());
} else {
cell.setCellValue("-");
}
}
} else if (usc.getAss_type().equalsIgnoreCase("file")) {
a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id());
String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a);
if (status.equalsIgnoreCase("ontime")
|| status.equalsIgnoreCase("hurryup")
|| status.equalsIgnoreCase("late")) {
cell.setCellValue(usc.getStf().getScore());
} else {
status = Assignment.calculateTime(a);
if (status.equalsIgnoreCase("miss")) {
cell.setCellValue(usc.getStf().getScore());
} else {
cell.setCellValue("-");
}
}
}
j++;
}
cell = sumRow.createCell(j);
int lastcol = account.getListStudentScore().size();
// calculate column
int dv = lastcol / 26;
String coltmp = "";
for (int i = 0; i < dv; i++) {
coltmp += "A";
}
coltmp += (char) ('A' + (lastcol - (dv * 26)));
System.out.println(coltmp);
//
String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1);
System.out.println(ref);
cell.setCellFormula("SUM(" + ref + ")");
rownum++;
}
// Write the output to a file
String filename = "scoresheet_" + c.getName() + ".xlsx";
String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename;
// String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
response.sendRedirect("file/scoresheet/" + filename);
//
// Workbook wb = new XSSFWorkbook();
// Sheet sheet = wb.createSheet("scoresheet");
// 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("Score sheet of " + "...." + " course");
// sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));
//
// //row with totals below
// int rownum = 2;
// Row sumRow = sheet.createRow(rownum);
// sumRow.setHeightInPoints(35);
// Cell cell;
// cell = sumRow.createCell(0);
// cell.setCellValue("Name:");
//
// for (int j = 1; j < 12; j++) {
// cell = sumRow.createCell(j);
// String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
// cell.setCellFormula("SUM(" + ref + ")");
// }
//
// // Write the output to a file
// String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
// FileOutputStream out = new FileOutputStream(file);
// wb.write(out);
// out.close();
}
@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 +++++++++++++");
}
