Java excel导出笔记
//导出表格数据 getInventoryItemData:function(){ var warehouse = YAHOO.util.Dom.get("warehouse").classObj.get('value').id; window.location.href=PORTAL.constant.CONTEXT_PATH+"/core/wm/materialInventory/inventoryStockAction!getInventoryItemData.action?warehouse="+warehouse; },
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 | /** * 查询物料盘点列表并导出 * @throws Exception */ public void getInventoryItemData(){ try { HttpServletRequest request = ServletActionContext.getRequest(); String warehouse = request.getParameter( "warehouse" ); SiteBO siteBo = this .getCurrentSite(); UserBO userBO = this .getSessionUser(); List<Object[]> datalist; datalist = inventoryStockActionService.getInventoryItemData(warehouse, siteBo, userBO); List<String> rowNames= new ArrayList<String>(Arrays.asList( "序号" , " 物料编号 " , "物料名称" , "工单号" , "批次" , "规格型号" , "单位" , "数量" )); // List<Object[]> objList=mapToObject(datalist,rowNames); export( "物料盘点列表" , rowNames,datalist); } catch (Exception ex) { this .simpleResponse( this .getJsonText(ex)); } } /* * 导出数据 */ public void export(String title,List<String> rowName,List<Object[]> dataList){ SXSSFWorkbook workbook = new SXSSFWorkbook(); // 创建工作簿对象 Sheet sheet = workbook.createSheet(title); // 创建工作表 try { // 产生表格标题行 Row rowm = sheet.createRow( 0 ); Cell cellTiltle = rowm.createCell( 0 ); //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 XSSFCellStyle columnTopStyle = this .getColumnTopStyle(workbook); //获取列头样式对象 XSSFCellStyle style = this .getStyle(workbook); //单元格样式对象 sheet.addMergedRegion( new CellRangeAddress( 0 , 1 , 0 , (rowName.size()- 1 ))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(title); // 定义所需列数 int columnNum = rowName.size(); Row rowRowName = sheet.createRow( 2 ); // 在索引2的位置创建行(最顶端的行开始的第二行) // 将列头设置到sheet的单元格中 for ( int n= 0 ;n<columnNum;n++){ Cell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型 HSSFRichTextString text = new HSSFRichTextString(rowName.get(n)); cellRowName.setCellValue(text); //设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式 } //将查询出的数据设置到sheet对应的单元格中 for ( int i= 0 ;i<dataList.size();i++){ Object[] obj=dataList.get(i); //遍历每个对象 Row row = sheet.createRow(i+ 3 ); //创建所需的行数 for ( int j= 0 ; j<obj.length; j++){ Cell cell = null ; //设置单元格的数据类型 if (j == 0 ){ cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(i+ 1 ); } else { cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING); cell.setCellValue(String.valueOf(obj[j])); //设置单元格的值 } cell.setCellStyle(style); //设置单元格样式 } } if (workbook != null ){ try { String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring( 4 , 13 ) + ".xlsx" ; String headStr = "attachment; filename=\"" + fileName + "\"" ; HttpServletResponse response = ServletActionContext.getResponse(); response.reset(); response.setContentType( "APPLICATION/OCTET-STREAM" ); response.setHeader( "Content-Disposition" , headStr); OutputStream out = response.getOutputStream(); workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } } catch (Exception e){ e.printStackTrace(); } } /* * 列头单元格样式 */ public XSSFCellStyle getColumnTopStyle(SXSSFWorkbook workbook) { // 设置字体 XSSFFont font = (XSSFFont) workbook.createFont(); //设置字体大小 font.setFontHeightInPoints(( short ) 11 ); //字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName( "Courier New" ); //设置样式; XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle(); //设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText( false ); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } /* * 列数据信息单元格样式 */ public XSSFCellStyle getStyle(SXSSFWorkbook workbook) { // 设置字体 XSSFFont font = (XSSFFont) workbook.createFont(); //设置字体大小 //font.setFontHeightInPoints((short)10); //字体加粗 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体名字 font.setFontName( "Courier New" ); //设置样式; XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle(); //设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); //设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); //设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); //设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText( false ); //设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步