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; 
       
    }

  

posted @   26键的人生  阅读(195)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示