EXCEL的数据中导入到我自己设定的EXCEL模板中

1、<% page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8"%><%page import="java.text.SimpleDateFormat"%> <%page import="org.apache.poi.ss.usermodel.CellStyle

2、"%><%page import="mon.InitApp"%><%page import="com.yinjun.service.ITSBalanceService"%><%page import="com.yinjun.entity.TSBalance"%><%page import="java.util.*"%><%page import="org.apache.poi.ss.us

3、ermodel.Cell"%><%page import="org.apache.poi.ss.usermodel.Row"%><%page import="org.apache.poi.ss.usermodel.Sheet"%><%page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%><%page import="org.apache.poi.ss.usermode

4、l.Workbook"%><%page import="java.io.IOException"%><%page import="java.io.FileInputStream"%> <%page import="java.io.InputStream"%>  <% page import="java.io.File"%><% page impor

5、t="com.yinjun.util.*"%><%    request.setCharacterEncoding("utf-8");    String msg = ""    String fileName = request.getParameter("fileName");    

6、; int index = fileName.lastIndexOf("");    if (index > 0)         fileName = fileName.substring(index + 1);         String

7、 title = new SimpleDateFormat("yyyyMMdd").format(new Date()            + fileName;    response.reset();    response.setCharacterEncoding("utf-8");

8、0;   response.setContentType("application/download");    response.setHeader("Content-Disposition", "attachment; filename="            + new String(title.ge

9、tBytes("utf-8"), "iso8859-1");/设置文件名显示中文    String startDate = request.getParameter("startDate");    String endDate = request.getParameter("endDate");    String 

10、;meterIds = request.getParameter("meterIds");    String pageSize = request.getParameter("pageSize");    String currentPage = request.getParameter("currentPage");    Strin

11、g statistics = request.getParameter("statistics");    String exportWay = request.getParameter("exportWay");     Map<String, String> map = new HashMap<String, String&g

12、t;();    map.put("startDate", startDate);    map.put("endDate", endDate);    map.put("meterIds", meterIds);    map.put("currentPage", currentPage);  &

13、#160; map.put("pageSize", pageSize);    if ("all".equals(exportWay)         map.put("queryAll", "queryAll");/选择查询全部        map.put(&quo

14、t;statistics", statistics);    ITSBalanceService tsbalanceService = (ITSBalanceService) InitApp.context            .getBean("tsbalanceService");    Map<Strin

15、g, Object> data = tsbalanceService.showTSBalance(map);    List<TSBalance> projectNodeList = (List<TSBalance>) data            .get("tsbalanceList"); &

16、#160;   String path = request.getRealPath("/excelTemplate").replace("",            "/");    String filePath = path + "/" 

17、;+ fileName;    File file = new File(filePath);     InputStream is = null;    try         is = new FileInputStream(file);  &#

18、160;     Workbook wb = new HSSFWorkbook(is);        int sheetsNum = wb.getNumberOfSheets();        /找到对应的sheet        fo

19、r (int i = 0; i < sheetsNum; i+)             String sheetName = wb.getSheetName(i);            int lastRowNum

20、60;= 0;            /如果是平衡报表            if (CommonUtil.SHEET_BALANCE.equals(sheetName)              &

21、#160;  Sheet sheet = wb.getSheet(sheetName);                lastRowNum = sheet.getLastRowNum();               

22、 /循环row，获得需要的cell                for (int r = 4; r <= lastRowNum; r+)                 &

23、#160;   Row row = sheet.getRow(r);                    /电表地址所在的单元格                 

24、60;  Cell meterCell = row.getCell(4);                    /正向有功起码所在的单元格,获得cell的样式                

25、;    Cell PP1Cell = row.getCell(7);                    CellStyle PP1CellStyle = PP1Cell.getCellStyle();        

26、;            /正向有功止码所在的单元格,获得cell的样式                    Cell PP2Cell = row.getCell(8);      

27、60;             CellStyle PP2CellStyle = PP2Cell.getCellStyle();                    /反向有功起码所在的单元格,获得cell的样式  &

28、#160;                 Cell PP4Cell = row.getCell(11);                    CellStyle PP4CellStyle

29、 = PP4Cell.getCellStyle();                    /反向有功止码所在的单元格,获得cell的样式                    C

30、ell PP5Cell = row.getCell(12);                    CellStyle PP5CellStyle = PP5Cell.getCellStyle();            

31、        /从电表地址单元格中取出值                    String meterValue = ""            

32、        /数值单元格取值                       if (meterCell.getCellType() = Cell.CELL_TYPE_NUMERIC)      &#

33、160;                  int cellValue = (int) meterCell                       

34、;         .getNumericCellValue();                        meterValue = Integer.toString(cellValue);    

35、60;                                   /文本单元格取值             &

36、#160;      else if (meterCell.getCellType() = Cell.CELL_TYPE_STRING)                         meterValue = meterCell

37、.getStringCellValue();                                          /从页面传的list中找到对应的电表地址，并将起码、

38、止码写入                    for (TSBalance balance : projectNodeList)                   &

39、#160;     /如果模板中的电表地址和list中的电表地址相等,将起码、止码写入对应cell中;                        String meterAddress = balance.getAddress()=null?"":bala

40、nce.getAddress();                        String pp1 = balance.getPP1()=null?"":balance.getPP1();          

41、;              String pp2 = balance.getPP2()=null?"":balance.getPP2();                      

42、60; String pp4 = balance.getPP4()=null?"":balance.getPP4();                        String pp5 = balance.getPP5()=null?"":bala

43、nce.getPP5();                        if (!"".equals(meterAddress)&& meterValue.equals(meterAddress)         

44、;                    /判断是否为-1,不为-1就修改单元格的值                          &#

45、160; if (!CommonUtil.judge(pp1)                                 PP1Cell.setCellValue(pp1);      

46、;                          PP1Cell.setCellStyle(PP1CellStyle);                  

47、                                      if (!CommonUtil.judge(pp2)       

48、;                          PP2Cell.setCellValue(pp2);                   

49、60;            PP2Cell.setCellStyle(PP2CellStyle);                               

50、0;                         if (!CommonUtil.judge(pp4)                   

51、60;             PP4Cell.setCellValue(pp4);                                P

52、P4Cell.setCellStyle(PP4CellStyle);                                            &#

53、160;            if (!CommonUtil.judge(pp5)                                &

54、#160;PP5Cell.setCellValue(pp5);                                PP5Cell.setCellStyle(PP5CellStyle);       

55、                                                  

56、                                                  

57、       /如果是10kv线损表            else if (CommonUtil.SHEET_LINE_LOSS.equals(sheetName)                 Sheet 

58、;sheet = wb.getSheet(sheetName);                lastRowNum = sheet.getLastRowNum();                for (int r&

59、#160;= 2; r < lastRowNum; r+)                     Row row = sheet.getRow(r);            

60、0;       /电表地址所在的单元格                                         

61、;Cell meterCell = row.getCell(4);                    /电量类的标识的单元格                    C

62、ell typeCell = row.getCell(3);                    /本月止码所在的单元格                    Cell

63、 checkCodeCell = row.getCell(6);                    CellStyle chcekCodeStyle = checkCodeCell.getCellStyle();          &#

64、160;         /本月起码所在的单元格                    Cell startCodeCell = row.getCell(5);          

65、          CellStyle startCodeStyle = startCodeCell.getCellStyle();                            &

66、#160;            /从电表地址单元格中取出值                    String meterValue = ""       &

67、#160;            /数值单元格取值                       if (meterCell.getCellType() = Cell.CELL_TYPE_NUMERIC) 

68、60;                       int cellValue = (int) meterCell.getNumericCellValue();              &

69、#160;         meterValue = Integer.toString(cellValue);                               

70、0;        /文本单元格取值                    else if (meterCell.getCellType() = Cell.CELL_TYPE_STRING)       

71、60;                 meterValue = meterCell.getStringCellValue();                       

72、60;                  /取电量标识值                    int typeValue = 0;     

73、;               /数值单元格取值                       if (typeCell.getCellType() = Cell.CELL_TYPE_NUME

74、RIC)                         typeValue = (int) typeCell.getNumericCellValue();             

75、0;                          /文本单元格取值                    else if&#

76、160;(typeCell.getCellType() = Cell.CELL_TYPE_STRING)                         typeValue = Integer.parseInt(typeCell.getStringCellValue();   &

77、#160;                                      /取本月止碼          &

78、#160;         String checkCodeValue = ""                    /数值单元格取值          &#

79、160;                                 if (checkCodeCell.getCellType() = Cell.CELL_TYPE_NUMERIC)    &#

80、160;                    checkCodeValue = Double.toString(checkCodeCell.getNumericCellValue();                &

81、#160;                       /文本单元格取值                    else if (checkCodeC

82、ell.getCellType() = Cell.CELL_TYPE_STRING)                         checkCodeValue = checkCodeCell.getStringCellValue();      

83、                                    /将本月止码移动到本月起码            

84、;        startCodeCell.setCellType(Cell.CELL_TYPE_STRING);                    startCodeCell.setCellValue(checkCodeValue);      

85、60;             startCodeCell.setCellStyle(startCodeStyle);                    for (TSBalance balance : projectNode

86、List)                         String meterAddress = balance.getAddress()=null?"":balance.getAddress();        

87、                String pp2 = balance.getPP2()=null?"":balance.getPP2();                    

88、0;   String pp5 = balance.getPP5()=null?"":balance.getPP5();                        String pp8 = balance.getPP8()=null?"

89、":balance.getPP8();                        String pp10 = balance.getPP10()=null?"":balance.getPP10();        &

90、#160;                                        if (!"".equals(meterAddress)&&&#

91、160;meterValue.equals(meterAddress)                             if(CommonUtil.judge(pp2)           &

92、#160;                    checkCodeCell.setCellValue("");                      &#

93、160;     else                                /正向有功           

94、;                     if (typeValue = CommonUtil.TYPE_PP2)                    &#

95、160;                checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);                         &

96、#160;          checkCodeCell.setCellValue(pp2);                                  

97、;  checkCodeCell.setCellStyle(chcekCodeStyle);                                        

98、0;                                               if(CommonUtil.ju

99、dge(pp5)                                checkCodeCell.setCellValue("");          

100、;                  else                               &

101、#160; /反向有功                                if (typeValue = CommonUtil.TYPE_PP5)       

102、;                              checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);           

103、0;                        checkCodeCell.setCellValue(pp5);                    &#

104、160;               checkCodeCell.setCellStyle(chcekCodeStyle);                           &

105、#160;                                                 &

106、#160;                                       if(CommonUtil.judge(pp8)      &

107、#160;                         checkCodeCell.setCellValue("");                 &#

108、160;              else                                 /正向无功 

109、0;                              if (typeValue = CommonUtil.TYPE_PP8)           &

110、#160;                         checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);                

111、                    checkCodeCell.setCellValue(pp8);                        

112、0;           checkCodeCell.setCellStyle(chcekCodeStyle);                               

113、60;                                                 

114、60;                                   if(CommonUtil.judge(pp10)          &#

115、160;                     checkCodeCell.setCellValue("");                     

116、60;      else                                 /反向无功         

117、                       if (typeValue = CommonUtil.TYPE_PP10)                  &#

118、160;                  checkCodeCell.setCellType(Cell.CELL_TYPE_STRING);                       &

119、#160;            checkCodeCell.setCellValue(pp10);                               

120、0;    checkCodeCell.setCellStyle(chcekCodeStyle);                                      

121、60;                                                 

122、60;                                            else     

123、;            msg = "你所提交的模板不可用"                break;              

124、60;             wb.write(response.getOutputStream();     catch (Exception e)         e.printStackTrace();     finally  &

125、#160;      try             if (is != null)                is.close();       

126、;  catch (IOException e)             e.printStackTrace();                out.clear();    out = pageContex

127、t.pushBody();%>public static HSSFWorkbook createExcel() throws IOException         HSSFWorkbook workbook = new HSSFWorkbook();        HSSFSheet sheet;  

128、      HSSFRow row;        HSSFCell cell;        sheet = workbook.createSheet();        row = sheet.createRow(0); &#

129、160;      cell = row.createCell(0);        cell.setCellValue("第一行第一列");        cell = row.createCell(1);        cell.set

130、CellValue("第一行第二列");        cell = row.createCell(2);        cell.setCellValue("第一行第三列");        cell = row.createCell(3);   

131、     cell.setCellValue("第一行第四列");        cell = row.createCell(4);        cell.setCellValue("第一行第五列");        cell =

132、0;row.createCell(5);        cell.setCellValue("第一行第六列");        for (int i = 0; i < 30000; i+)             row = sheet.createRow(i + 1);            cell = row.createCell(0);            cell.setCellValue("123");