导入模板设值导出poi

1. import java.io.IOException;  
2. import java.io.InputStream;  
3. import java.io.OutputStream;  
4. import java.math.BigDecimal;  
5. import java.util.List;  
6. import java.util.Map;  
7.   
8. import javax.servlet.http.HttpServletRequest;  
9.   
10. import org.apache.poi.ss.usermodel.Cell;  
11. import org.apache.poi.ss.usermodel.Row;  
12. import org.apache.poi.ss.usermodel.Sheet;  
13. import org.apache.poi.ss.usermodel.Workbook;  
14. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
15.   
16. public class ExportTempExcelXlsx {  
17.   
18.     /** 
19.      * 将读取到的文件重新写入新的文件 
20.      *  
21.      * @param output 
22.      * @param request  
23.      *  
24.      * @param bw 
25.      */  
26.     public void createExcel(List<Map<String, String>> list, OutputStream output, HttpServletRequest request) {  
27.   
28.         Workbook tempWorkBook = null;  
29.         Sheet tempSheet = null;  
30.         int rowIndex = 5;  
31.         Row tempRow = null;  
32.         Cell tempCell = null;  
33.         InputStream inputstream = null;  
34.         try {  
35.             inputstream = request.getSession().getServletContext().getResourceAsStream("/templates/FZL-SUMMARY.xlsx");  
36.             // 获取模板  
37.             tempWorkBook = new XSSFWorkbook(inputstream);  
38.             // 获取模板sheet页  
39.             tempSheet = tempWorkBook.getSheetAt(0);  
40.   
41.             // 用于统计的变量  
42.             double preAmount = 0;  
43.             double plusAmount = 0;  
44.             double reduceAmount = 0;  
45.             double afterAmount = 0;  
46.   
47.             // 将数据写入excel  
48.             for (int i = 0; i < list.size(); i++) {  
49.   
50.                 int cellNo = 1;  
51.                 // 获取指定行  
52.                 tempRow = tempSheet.getRow(rowIndex++);  
53.                 // 获取指定行的单元格  
54.                 tempCell = tempRow.getCell(cellNo++);  
55.                 // 给单元格设值  
56.                 tempCell.setCellValue(getAccountsType(Integer.valueOf(list.get(i).get("accountsType"))));  
57.   
58.                 double curpreAmount = Double.valueOf(list.get(i).get("preAmount"));  
59.                 tempCell = tempRow.getCell(cellNo++);  
60.                 tempCell.setCellValue(new BigDecimal(curpreAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
61.   
62.                 double curplusAmount = Double.valueOf(list.get(i).get(  
63.                         "plusAmount"));  
64.                 tempCell = tempRow.getCell(cellNo++);  
65.                 tempCell.setCellValue(new BigDecimal(curplusAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
66.   
67.                 double curreduceAmount = Double.valueOf(list.get(i).get("reduceAmount"));  
68.                 tempCell = tempRow.getCell(cellNo++);  
69.                 tempCell.setCellValue(new BigDecimal(curreduceAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
70.   
71.                 cellNo++;  
72.   
73.                 double curafterAmount = Double.valueOf(list.get(i).get("afterAmount"));  
74.                 tempCell = tempRow.getCell(cellNo++);  
75.                 tempCell.setCellValue(new BigDecimal(curafterAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
76.   
77.                 plusAmount += Double.valueOf(list.get(i).get("preAmount"));  
78.                 reduceAmount += Double.valueOf(list.get(i).get("plusAmount"));  
79.                 afterAmount += Double.valueOf(list.get(i).get("reduceAmount"));  
80.                 preAmount += Double.valueOf(list.get(i).get("afterAmount"));  
81.   
82.                 if (i == (list.size() - 1)) {  
83.   
84.                     // 创建一行 统计  
85.                     tempRow = tempSheet.getRow(rowIndex);  
86.                     int cellNo2 = 2;  
87.                     // 获取单元格  
88.                     tempCell = tempRow.getCell(cellNo2++);  
89.                     // 给单元格设值  
90.                     tempCell.setCellValue(new BigDecimal(plusAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
91.                     tempCell = tempRow.getCell(cellNo2++);  
92.                     tempCell.setCellValue(new BigDecimal(reduceAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
93.                     tempCell = tempRow.getCell(cellNo2++);  
94.                     tempCell.setCellValue(new BigDecimal(afterAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
95.                     cellNo2++;  
96.                     tempCell = tempRow.getCell(cellNo2++);  
97.                     tempCell.setCellValue(new BigDecimal(preAmount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());  
98.                 }  
99.   
100.             }  
101.             // 将内容写入Excel  
102.             tempWorkBook.write(output);  
103.         } catch (Exception e) {  
104.             e.printStackTrace();  
105.         } finally {  
106.             try {  
107.                 output.close();  
108.             } catch (IOException e) {  
109.                 e.printStackTrace();  
110.             }  
111.         }  
112.   
113.     }  
114.   
115.       
116. }