List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setDefaultColumnWidth
@Override public void setDefaultColumnWidth(int width)
From source file:com.hp.action.TimeKeeperAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize//w w w . java 2s.co m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA timeKeeperList = (List<TimeKeeper>) session.getAttribute("timeKeeperList"); if (timeKeeperList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Chm cng"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co chm cng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < timeKeeperList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(timeKeeperList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoChamCong" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.huateng.struts.query.action.T50202Action.java
License:Open Source License
public String download() { String brhBelow = InformationUtil.getBrhGroupString(brhId); System.out.println(brhBelow); String thisMonStart = date + "01"; String thisMonEnd = date + "31"; String lastYear = String.valueOf(Integer.parseInt(date.substring(0, 4)) - 1) + "1231"; String lastMon = thisMonStart;//start? String thisYearStart = date.substring(0, 4) + "0101"; String thisYearEnd = date.substring(0, 4) + "1231"; try {/* ww w .j a v a 2 s. com*/ //? String[][] data = new String[40][9]; //?data for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { if (j < 4) { data[i][j] = ""; } else { data[i][j] = "0"; } } } Iterator it = null; //??? //?data data[0][0] = "1"; data[0][1] = "?"; data[0][3] = ""; data[1][1] = ""; data[1][2] = "?"; data[1][3] = ""; data[2][2] = "?"; data[2][3] = ""; String sql = "select CONN_TYPE, " + "sum(case when apply_date <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when apply_date < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; List list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 1; } else { index = 2; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[0][4] = String.valueOf(Integer.valueOf(data[1][4]) + Integer.valueOf(data[2][4])); data[0][5] = String.valueOf(Integer.valueOf(data[1][5]) + Integer.valueOf(data[2][5])); data[0][6] = String.valueOf(Integer.valueOf(data[1][6]) + Integer.valueOf(data[2][6])); data[0][7] = String.valueOf(Integer.valueOf(data[1][7]) + Integer.valueOf(data[2][7])); data[0][8] = String.valueOf(Integer.valueOf(data[1][8]) + Integer.valueOf(data[2][8])); } //??? //?data data[3][0] = "2"; data[3][1] = "?POS"; data[3][3] = "?"; data[4][1] = ""; data[4][2] = "?"; data[4][3] = "?"; data[5][2] = "?"; data[5][3] = "?"; sql = "select CONN_TYPE ," + "sum(case when t.REC_CRT_TS <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when t.REC_CRT_TS < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_TERM_INF t,TBL_MCHT_BASE_INF m where t.MCHT_CD = m.MCHT_NO " + "and m.ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 4; } else { index = 5; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[3][4] = String.valueOf(Integer.valueOf(data[4][4]) + Integer.valueOf(data[5][4])); data[3][5] = String.valueOf(Integer.valueOf(data[4][5]) + Integer.valueOf(data[5][5])); data[3][6] = String.valueOf(Integer.valueOf(data[4][6]) + Integer.valueOf(data[5][6])); data[3][7] = String.valueOf(Integer.valueOf(data[4][7]) + Integer.valueOf(data[5][7])); data[3][8] = String.valueOf(Integer.valueOf(data[4][8]) + Integer.valueOf(data[5][8])); } //??? //?data data[6][0] = "3"; data[6][1] = "?"; sql = "SELECT DESCR,nvl(mchnt1.c,0),nvl(mchnt2.c,0),nvl(mchnt3.c,0),nvl(mchnt4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date <= '" + lastYear + "' group by MCHT_GRP) mchnt1 on (grp.MCHNT_TP_GRP = mchnt1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date < '" + lastMon + "' group by MCHT_GRP) mchnt2 on (grp.MCHNT_TP_GRP = mchnt2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "' group by MCHT_GRP) mchnt3 on (grp.MCHNT_TP_GRP = mchnt3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "' group by MCHT_GRP) mchnt4 on (grp.MCHNT_TP_GRP = mchnt4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); int index = 6; int len = 0; if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = ""; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; len++; } } data[index][0] = "4"; data[index][1] = "?POS"; sql = "SELECT DESCR,nvl(term1.c,0),nvl(term2.c,0),nvl(term3.c,0),nvl(term4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS <= '" + lastYear + "' group by MCHT_GRP) term1 on (grp.MCHNT_TP_GRP = term1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS < '" + lastMon + "' group by MCHT_GRP) term2 on (grp.MCHNT_TP_GRP = term2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "' group by MCHT_GRP) term3 on (grp.MCHNT_TP_GRP = term3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "' group by MCHT_GRP) term4 on (grp.MCHNT_TP_GRP = term4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = "?"; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = null; HSSFCell cell = null; sheet.setDefaultColumnWidth(10); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 6000); //? HSSFCellStyle styleTitle = ExcelUtil.createStyleTitle(workbook); HSSFCellStyle styleBold = ExcelUtil.createStyleBold(workbook); HSSFCellStyle styleCenter = ExcelUtil.createStyleCenter(workbook); HSSFCellStyle styleRight = ExcelUtil.createStyleRight(workbook); HSSFCellStyle styleThin = ExcelUtil.createStyleThinCenter(workbook); short rowIndex = 0;// // row = sheet.createRow(rowIndex++); row.setHeight((short) 800); cell = row.createCell(0); cell.setCellStyle(styleTitle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("?"); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));// ?? rowIndex++; row = sheet.createRow(rowIndex++); cell = row.createCell(0); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(1); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(date.substring(0, 4) + "" + date.substring(4) + ""); cell = row.createCell(3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(InformationUtil.getBrhName(brhId)); String[] titles = { "??", " ", "", "??", "", "", "", "", "" }; row = sheet.createRow(rowIndex++); for (int i = 0; i < titles.length; i++) { cell = row.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titles[i]); cell.setCellStyle(styleBold); } for (int i = 0; i < index; i++) { row = sheet.createRow(rowIndex++); for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); if (j < 4) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleCenter); cell.setCellValue(data[i][j]); } else { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle(styleRight); cell.setCellValue(Double.valueOf(data[i][j])); } } } int start = 1; sheet.addMergedRegion(new CellRangeAddress(start + 1, start + 1, 4, 8)); sheet.addMergedRegion(new CellRangeAddress(start + 2, start + 2, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 3, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 5, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 4, start + 5, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 6, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 8, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 7, start + 8, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 1, 1)); String path = ExcelUtil.writeFiles(workbook, "RN50202RN_" + brhId + "_" + CommonFunction.getCurrentDateTime() + ".xls"); return returnService(Constants.SUCCESS_CODE_CUSTOMIZE + path); } catch (Exception e) { e.printStackTrace(); return returnService("?,?", e); } }
From source file:com.itn.excelDao.ExcelView.java
@Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest requesr, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Users> allUsers = (List<Users>) model.get("allUsers"); //Create new excel sheet HSSFSheet sheet = workbook.createSheet("Java Books"); sheet.setDefaultColumnWidth(30); //create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Arial"); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font);/* ww w . j a va2 s .c o m*/ // create header row HSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("id"); header.getCell(0).setCellStyle(style); header.createCell(1).setCellValue("First Name"); header.getCell(1).setCellStyle(style); header.createCell(2).setCellValue("Last Name"); header.getCell(2).setCellStyle(style); header.createCell(3).setCellValue("Email Address"); header.getCell(3).setCellStyle(style); // create data rows int rowCount = 1; for (Users aUsers : allUsers) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(aUsers.getId()); aRow.createCell(1).setCellValue(aUsers.getFirstName()); aRow.createCell(2).setCellValue(aUsers.getLastName()); aRow.createCell(3).setCellValue(aUsers.getEmail()); } }
From source file:com.leosys.core.utils.ExcelUtil.java
public void exportExcel(List<?> dataList, OutputStream out) throws Exception { HSSFWorkbook workbook = null;//from w w w .j av a2 s . c o m HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; HSSFCellStyle titleStyle = null; int rowIndex = 0; try { workbook = new HSSFWorkbook();// sheet = workbook.createSheet("?");// ? sheet.setDefaultColumnWidth((short) 30);// 15 titleStyle = workbook.createCellStyle();//? titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setFillForegroundColor(HSSFColor.WHITE.index); titleStyle.setFillBackgroundColor(HSSFColor.WHITE.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 17); titleStyle.setFont(font); row = sheet.createRow(rowIndex++); row.setHeight((short) 600); for (short i = 0; i < headArr.length; i++) { cell = row.createCell(i); //? if (i == 0) cell.setCellValue(new HSSFRichTextString(title)); cell.setCellStyle(titleStyle); } // ??? sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headArr.length - 1))); titleStyle = workbook.createCellStyle(); titleStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setFontHeightInPoints((short) 13); titleStyle.setFont(font);// ?? row = sheet.createRow(rowIndex++);// for (short i = 0; i < headArr.length; i++) { cell = row.createCell(i); cell.setCellStyle(titleStyle); cell.setCellValue(new HSSFRichTextString(headArr[i])); } //? titleStyle = workbook.createCellStyle(); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); if (dataList == null || dataList.isEmpty()) return; short dataType = 0;// if (dataList.get(0) instanceof Map<?, ?>) dataType = 1; else if (dataList.get(0) instanceof List<?>) dataType = 2; if (dataType == 0) { String[] dataArr = null; for (Object data : dataList) { dataArr = (String[]) data; if (dataArr == null) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < dataArr.length) { Object val = dataArr[i]; if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(dataArr[i], i, dataArr); fillCell(row, titleStyle, font, i, val); } } } } else if (dataType == 1) { Map<?, ?> map = null; for (Object data : dataList) { map = (Map<?, ?>) data; if (map == null) continue; Object[] dataArr = map.values().toArray(); if (dataArr == null) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < dataArr.length) { Object val = dataArr[i]; if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(dataArr[i], i, dataArr); fillCell(row, titleStyle, font, i, val); } } } } else if (dataType == 2) { List<?> list = null; for (Object data : dataList) { list = (List<?>) data; if (list == null || list.isEmpty()) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < list.size()) { Object val = list.get(i); if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(list.get(i), i, list); fillCell(row, titleStyle, font, i, val); } } } } else throw new Exception("excel???"); workbook.write(out); } catch (Exception e) { throw new Exception("excel" + e.getMessage()); } finally { // if(out != null){ // try { // out.close(); // } catch (IOException e) {} // } } }
From source file:com.ssic.education.provider.controller.ProSupplierController.java
@RequestMapping(value = "/excel") @ResponseBody//from ww w.ja v a2s . c om public ModelAndView exportExcel(SupplierDto supplierDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } supplierDto.setReceiverId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("????"); titles.add("???"); // titles.add("????"); // titles.add("??????"); titles.add("????"); titles.add("???"); // titles.add("?"); // titles.add("???"); titles.add("?"); titles.add("?"); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataGrid dg = supplierService.findProSupplier(supplierDto, null); List<SupplierDto> expList = dg.getRows(); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getSupplierName()); vpd.put("var2", expList.get(i).getAddress()); // vpd.put("var3", expList.get(i).getFoodServiceCode()); // vpd.put("var4", expList.get(i).getFoodBusinessCode()); vpd.put("var3", expList.get(i).getFoodCirculationCode()); vpd.put("var4", expList.get(i).getFoodProduceCode()); // vpd.put("var7", expList.get(i).getBusinessLicense()); // vpd.put("var8", expList.get(i).getSupplierCode()); vpd.put("var5", expList.get(i).getCorporation()); vpd.put("var6", expList.get(i).getContactWay()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.ssic.education.provider.controller.WaresController.java
@RequestMapping(value = "/excel") @ResponseBody/* w w w. ja v a 2s .c o m*/ public ModelAndView exportExcel(ProWaresDto proWaresDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } proWaresDto.setSupplierId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("??"); titles.add("???"); titles.add(""); titles.add("?"); titles.add("?"); // titles.add("??"); // titles.add("???"); // titles.add("??"); titles.add("?"); titles.add("???"); titles.add(""); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<ProWaresDto> expList = waresService.findAllWares(proWaresDto, null); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getWaresName()); vpd.put("var2", expList.get(i).getAmountUnit()); vpd.put("var3", expList.get(i).getSpec()); vpd.put("var4", ProductClass.getName(expList.get(i).getWaresType())); vpd.put("var5", expList.get(i).getManufacturer()); // vpd.put("var5", expList.get(i).getEnName()); // vpd.put("var6", expList.get(i).getBarCode()); // vpd.put("var7", expList.get(i).getCustomCode()); vpd.put("var6", expList.get(i).getShelfLife()); vpd.put("var7", expList.get(i).getUnit()); vpd.put("var8", expList.get(i).getPlace()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.xhsoft.framework.common.views.Excel.ExcelView.java
License:Open Source License
@Override protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception { HSSFSheet sheet; HSSFRow sheetRow;//from w w w. j a v a2 s . c o m HSSFCell cell; // Go to the first sheet // getSheetAt: only if wb is created from an existing document // sheet = wb.getSheetAt(0); sheet = wb.createSheet("Spring"); sheet.setDefaultColumnWidth((short) 12); // write a text at A1 cell = getCell(sheet, 0, 0); setText(cell, "Spring-Excel test"); List words = (List) model.get("wordList"); for (int i = 0; i < words.size(); i++) { cell = getCell(sheet, 2 + i, 0); setText(cell, (String) words.get(i)); } }
From source file:com.zrx.authority.util.ObjectExcelView.java
@Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // TODO Auto-generated method stub Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell;/*ww w.ja v a2 s. c o m*/ response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell, title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<PageData> varList = (List<PageData>) model.get("varList"); int varCount = varList.size(); for (int i = 0; i < varCount; i++) { PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = getCell(sheet, i + 1, j); cell.setCellStyle(contentStyle); setText(cell, varstr); } } }
From source file:egovframework.rte.tex.com.web.EgovExcel.java
License:Apache License
/** * ?? ?./*from w ww. j a v a 2 s .co m*/ * @param model * @param wb * @param request * @param response * @throws Exception */ @SuppressWarnings({ "deprecation", "unchecked" }) @Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFCell cell = null; HSSFSheet sheet = wb.createSheet("Goods List"); sheet.setDefaultColumnWidth((short) 12); // put text in first cell cell = getCell(sheet, 0, 0); setText(cell, "Goods List"); // set header information setText(getCell(sheet, 2, 0), "No."); setText(getCell(sheet, 2, 1), "NAME"); setText(getCell(sheet, 2, 2), "PRICE"); setText(getCell(sheet, 2, 3), "CATEGORY NAME"); setText(getCell(sheet, 2, 4), "MAKER"); List<GoodsVO> goods = (List<GoodsVO>) model.get("goodsList"); for (int i = 0; i < goods.size(); i++) { GoodsVO goodsVO = goods.get(i); cell = getCell(sheet, 3 + i, 0); setText(cell, Integer.toString(i + 1)); cell = getCell(sheet, 3 + i, 1); setText(cell, goodsVO.getGoodsNm()); cell = getCell(sheet, 3 + i, 2); setText(cell, Integer.toString(goodsVO.getPrice())); cell = getCell(sheet, 3 + i, 3); setText(cell, goodsVO.getCategoryVO().getCtgryNm()); cell = getCell(sheet, 3 + i, 4); setText(cell, goodsVO.getMakr()); } }
From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * Creates the header rows for a sheet.// ww w .ja v a 2 s.c o m * * @param sheet the sheet to add header rows for * @param weekStartDate the start date for the week that is used to create * the date headers on the top * @param styleMap the style map for the header styles */ protected void createHeaderRows(HSSFSheet sheet, Date weekStartDate, Map<String, HSSFCellStyle> styleMap) { HSSFRow headerRow = sheet.createRow(0); Calendar calendarDay = Calendar.getInstance(); calendarDay.setTime(weekStartDate); sheet.setDefaultColumnWidth((short) 9); HSSFCell cell = headerRow.createCell((short) 0); cell.setCellStyle((HSSFCellStyle) styleMap.get("topLeftStyle")); cell.setCellValue(""); sheet.setColumnWidth((short) 0, (short) 10000); cell = headerRow.createCell((short) 1); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Monday"); cell = headerRow.createCell((short) 2); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Tuesday"); cell = headerRow.createCell((short) 3); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Wednesday"); cell = headerRow.createCell((short) 4); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Thusday"); cell = headerRow.createCell((short) 5); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Friday"); cell = headerRow.createCell((short) 6); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Saturday"); cell = headerRow.createCell((short) 7); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Sunday"); cell = headerRow.createCell(MAX_COLUMN); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRightStyle")); cell.setCellValue(""); sheet.setColumnWidth(MAX_COLUMN, (short) 1500); headerRow = sheet.createRow(1); cell = headerRow.createCell((short) 0); cell.setCellValue("Task Name"); cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameHeaderStyle")); cell = headerRow.createCell((short) 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 2); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 3); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 4); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 5); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 6); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 7); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell(MAX_COLUMN); cell.setCellValue("Total"); cell.setCellStyle((HSSFCellStyle) styleMap.get("totalHeaderStyle")); }