java实现excel的导入导出(poi详解)

1、 重庆达渝仁科技官网：经过两天的研究，现在对excel导出有点心得了。我们使用的excel导出的jar包是poi这个阿帕奇公司的一个项目，后来被扩充了。是比较好用的excel导出工具。下面来认识一下这个它吧。我们知道要创建一张excel你得知道excel由什么组成，比如说sheet也就是一个工作表格，例如一行，一个单元格，单元格格式，单元格内容格式这些都对应着poi里面的一个类。一个excel表格：HSSFWorkbook wb = new HSSFWorkbook();一个工作表格（sheet）：HSSFSheet sheet = wb.createSheet("测试表格"

2、;);一行（row）：HSSFRow row1 = sheet.createRow(0);一个单元格（cell）：HSSFCell cell2 = row2.createCell(short)0)单元格格式（cellstyle）：HSSFCellStyle style4 = wb.createCellStyle()单元格内容格式（）HSSFDataFormat format= wb.createDataFormat();知道上面的基本知识后下面学起来就轻松了。我直接贴代码，这段代码会产生一个表格其实，代码长，但是很简单，一看就明白Java代码java view plaincopyprint?1

3、. <span style="font-family:Microsoft YaHei;font-size:12px;">import ins.framework.dao.GenericDaoHibernate;    2.     3. import java.io.FileOutputStream;    4. import java.io.IOException;  &#

4、160; 5. import java.sql.Connection;    6. import java.sql.ResultSet;    7. import java.sql.SQLException;    8. import java.sql.Statement;    9. import java.util.ArrayList;   &

5、#160;10. import java.util.Date;    11. import java.util.List;    12.     13. import javax.annotation.Resource;    14.     15. import org.apache.poi.hssf.usermodel.HSSFCell; &#

6、160;  16. import org.apache.poi.hssf.usermodel.HSSFCellStyle;    17. import org.apache.poi.hssf.usermodel.HSSFDataFormat;    18. import org.apache.poi.hssf.usermodel.HSSFRow;    19. import org.apache.poi.hssf.u

7、sermodel.HSSFSheet;    20. import org.apache.poi.hssf.usermodel.HSSFWorkbook;    21. import org.apache.poi.hssf.util.Region;    22. import org.apache.poi.ss.usermodel.Font;    23. import org.hibernate

8、.HibernateException;    24. import org.hibernate.SQLQuery;    25. import org.hibernate.Session;    26. import org.springframework.orm.hibernate3.HibernateCallback;    27. import org.springframework.or

9、m.hibernate3.HibernateTemplate;    28. import org.springframework.stereotype.Component;    29.     30. import com.reportforms.service.facade.FundDayDetailService;    31. Component("fundDayDetailService&quo

10、t;)    32. public class FundDayDetailsServiceSpringImpl implements FundDayDetailService     33.     Resource    34.     private HibernateTemplate hibernateTemplate;&

11、#160;   35.     public HibernateTemplate getHibernateTemplate()     36.         return hibernateTemplate;    37.         38.

12、    public void setHibernateTemplate(HibernateTemplate hibernateTemplate)     39.         this.hibernateTemplate = hibernateTemplate;    40.      

13、;   41.     42.     SuppressWarnings("deprecation")     43.     public void outExcel(String fundsType, Date tradeDate, String assetsTypeCode)  &#

14、160; 44.             45.         HSSFWorkbook wb = new HSSFWorkbook();  /->创建了一个excel文件     46.        

15、0;HSSFSheet sheet = wb.createSheet("理财资金报表");   /->创建了一个工作簿     47.         HSSFDataFormat format= wb.createDataFormat();   /->单元格内容格式     48.  

16、       sheet.setColumnWidth(short)3, 20* 256);    /-设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的     49.         sheet.setColumnWidth(short)4, 20* 256); 

17、60;  /->第一个参数是指哪个单元格，第二个参数是单元格的宽度     50.         sheet.setDefaultRowHeight(short)300);    / ->有得时候你想设置统一单元格的高度，就用这个方法     51.         

18、0;   52.         /样式1     53.         HSSFCellStyle style = wb.createCellStyle(); / 样式对象     54.        &

19、#160;style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);/ 垂直     55.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);/ 水平     56.         /设置标题字体格式 

20、60;   57.         Font font = wb.createFont();       58.         /设置字体样式      59.         font.

21、setFontHeightInPoints(short)20);   /->设置字体大小     60.         font.setFontName("Courier New");   /-设置字体，是什么类型例如：宋体     61.         fo

22、nt1.setItalic(true);     /->设置是否是加粗     62.         style1.setFont(font1);     /->将字体格式加入到style1中        63.       

23、0; /style1.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex();     64.         /style1.setFillPattern(CellStyle.SOLID_FOREGROUND);设置单元格颜色     65.         style1.setWr

24、apText(true);   /设置是否能够换行，能够换行为true     66.         style1.setBorderBottom(short)1);   /设置下划线，参数是黑线的宽度     67.         style1.setBorderLeft(short)1);&

25、#160;  /设置左边框     68.         style1.setBorderRight(short)1);   /设置有边框     69.         style1.setBorderTop(short)1);   /设置下边框   &#

26、160; 70.         style4.setDataFormat(format.getFormat("￥#,#0");    /->设置为单元格内容为货币格式     71.            72.       

27、60; style5.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%");    /->设置单元格内容为百分数格式     73.             74.             

28、;75.         /表格第一行     76.         HSSFRow row1 = sheet.createRow(0);   /->创建一行     77.         / 四个参

29、数分别是：起始行，起始列，结束行，结束列     78.         sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 15);    79.         row1.setHeightInPoints(25); &#

30、160;  80.         HSSFCell cell1 = row1.createCell(short)0);   /->创建一个单元格     81.             82.       

31、60; cell1.setCellStyle(style);    83.         cell1.setCellValue("总公司资金运用日报明细表（理财资金）");    84.             85.      

32、0;  /表格第二行     86.         sheet.addMergedRegion(new Region(1,(short)0,1,(short)15);    87.         HSSFRow row2 = sheet.createRow(1);  

33、0; 88.         HSSFCell cell2 = row2.createCell(short)0);    89.         cell2.setCellValue("报告日期："+new Date();    90.     &#

34、160;   cell2.setCellStyle(style2);    91.             92.         /表格第三行     93.         sheet.addMergedR

35、egion(new Region(2,(short)0,2,(short)15);    94.         HSSFRow row3 = sheet.createRow(2);    95.         HSSFCell cell3 = row3.createCell(short

36、)0);    96.         cell3.setCellValue("交易日期："+new Date();    97.         cell3.setCellStyle(style2);    98.       

37、      99.         /表格第四行     100.         sheet.addMergedRegion(new Region(3, (short)0, 3, (short)2);    101.    &#

38、160;    HSSFRow row4 = sheet.createRow(3);    102.         row4.setHeightInPoints(short)75);    103.         HSSFCell cell4 = row4

39、.createCell(short)0);    104.         HSSFCell cell4_0_1 = row4.createCell(short)1);    105.         cell4_0_1.setCellStyle(style2);    106. 

40、0;       HSSFCell cell4_0_2 = row4.createCell(short)2);    107.         cell4_0_2.setCellStyle(style2);    108.         cell4.setC

41、ellStyle(style1);    109.         cell4.setCellValue("代码/品种");    110.             111.         HSSFCell 

42、cell4_1 = row4.createCell(short)3);    112.         cell4_1.setCellStyle(style1);    113.         cell4_1.setCellValue("投资类型");    114. 

43、0;           115.         HSSFCell cell4_2 = row4.createCell(short)4);    116.         cell4_2.setCellStyle(style1);  &#

44、160; 117.         cell4_2.setCellValue("证券账户");    118.             119.         HSSFCell cell4_3 = row4.creat

45、eCell(short)5);    120.         cell4_3.setCellStyle(style1);    121.         cell4_3.setCellValue("份额n单位：元");    122.      &

46、#160;      123.         HSSFCell cell4_4 = row4.createCell(short)6);    124.         cell4_4.setCellStyle(style1);    125.   

47、      cell4_4.setCellValue("结转总成本n单位：元");    126.             127.         HSSFCell cell4_5 = row4.createCell(short)7); 

48、   128.         cell4_5.setCellStyle(style1);    129.         cell4_5.setCellValue("总市值n单位：元");    130.         

49、60;   131.         HSSFCell cell4_6 = row4.createCell(short)8);    132.         cell4_6.setCellStyle(style1);    133.      &#

50、160;  cell4_6.setCellValue("结转成本价n单位：元");    134.             135.         HSSFCell cell4_7 = row4.createCell(short)9);    13

51、6.         cell4_7.setCellStyle(style1);    137.         cell4_7.setCellValue("市价n单位：元");    138.             

52、139.         HSSFCell cell4_8 = row4.createCell(short)10);    140.         cell4_8.setCellStyle(style1);    141.         cell

53、4_8.setCellValue("持有期收益n单位：%");    142.             143.         HSSFCell cell4_9 = row4.createCell(short)11);    144.   

54、0;     cell4_9.setCellStyle(style1);    145.         cell4_9.setCellValue("总收益率(总收益/结转总成本)n单位：%");    146.             147. &

55、#160;       HSSFCell cell4_10 = row4.createCell(short)12);    148.         cell4_10.setCellStyle(style1);    149.         cell4_10

56、.setCellValue("前一日涨跌幅n单位：%");    150.             151.         HSSFCell cell4_11 = row4.createCell(short)13);    152.    

57、;     cell4_11.setCellStyle(style1);    153.         cell4_11.setCellValue("盈亏n单位：元");    154.             155.   &

58、#160;     HSSFCell cell4_12 = row4.createCell(short)14);    156.         cell4_12.setCellStyle(style1);    157.         cell4_12.setCellValu

59、e("以实现收益n单位：元");    158.             159.         HSSFCell cell4_13 = row4.createCell(short)15);    160.      

60、   cell4_13.setCellStyle(style1);    161.         cell4_13.setCellValue("浮盈（亏）+已实现收益n单位：元");    162.             163.    

61、;     /第五行     164.         sheet.addMergedRegion(new Region(4, (short)0, 4, (short)2);    165.         HSSFRow row5 = s

62、heet.createRow(4);    166.         HSSFCell cell5 = row5.createCell(short)0);    167.         HSSFCell cell5_1 = row5.createCell(short)1);  

63、  168.         cell5_1.setCellStyle(style2);    169.         HSSFCell cell5_2 = row5.createCell(short)2);    170.        

64、; cell5_2.setCellStyle(style2);    171.         cell5.setCellValue("投资资产合计");    172.         cell5.setCellStyle(style2);    173.   

65、60;         174.         /第六行     175.         sheet.addMergedRegion(new Region(5, (short)0, 5, (short)2);    176.

66、        HSSFRow row6 = sheet.createRow(5);    177.         HSSFCell cell6 = row6.createCell(short)0);    178.       

67、0; HSSFCell cell6_1 = row6.createCell(short)1);    179.         cell6_1.setCellStyle(style2);    180.         HSSFCell cell6_2 = row6.createCell(

68、short)2);    181.         cell6_2.setCellStyle(style2);    182.         cell6.setCellValue("2、股票");    183.        

69、 cell6.setCellStyle(style2);    184.             185.         /第七行     186.         sheet.addMergedRegion(new 

70、Region(6, (short)0, 6, (short)2);    187.         HSSFRow row7 = sheet.createRow(6);    188.         HSSFCell cell7 = row7.createCell(s

71、hort)0);    189.         HSSFCell cell7_1 = row7.createCell(short)1);    190.         cell7_1.setCellStyle(style2);    191.    

72、60;    HSSFCell cell7_2 = row7.createCell(short)2);    192.         cell7_2.setCellStyle(style2);    193.         cell7.setCellValue("2.1、境内

73、A股");    194.         cell7.setCellStyle(style2);    195.             196.         /第八行     197

74、.         sheet.addMergedRegion(new Region(7, (short)0, 7, (short)2);    198.         HSSFRow row8 = sheet.createRow(7);    199.   

75、0;     HSSFCell cell8 = row8.createCell(short)0);    200.         HSSFCell cell8_1 = row8.createCell(short)1);    201.        

76、0;cell8_1.setCellStyle(style2);    202.         HSSFCell cell8_2 = row8.createCell(short)2);    203.         cell8_2.setCellStyle(style2);    204

77、.         cell8.setCellValue("非限售股");    205.         cell8.setCellStyle(style2);    206.             207.

78、60;       Connection conn = null;    208.         Statement sm = null;    209.         ResultSet rs =

79、0;null;    210.         try    211.             conn = hibernateTemplate.getSessionFactory().openSession().connection();    212. &

80、#160;           sm = conn.createStatement();    213.             rs = sm.executeQuery(sql);    214.     &

81、#160;           215.             int j = 0;   /增加行     216.             whi

82、le(rs.next()               217.                 HSSFRow rowN = sheet.createRow(8+j);   /第9行.第n行    &

83、#160;218.                 List<String> list = new ArrayList<String>();   /存放每一行数据     219.           &#

84、160;     for(int i = 1  i <= 16  i+ )    220.                     list.add(rs.getString(i);  

85、;  221.                     222.                     223.      &#

86、160;          for(int k = 0  k < 16  k+)    224.                     if(k<5)

87、60;                        225.                         HSSF

88、Cell cellN = rowN.createCell(short)k);    226.                         cellN.setCellStyle(style3);    227.    

89、                     cellN.setCellValue(list.get(k);    228.                    

90、     229.                     if(k>=5 && k<=9)|(k>=13)    230.            

91、;             HSSFCell cellN = rowN.createCell(short)k);    231.                       

92、60; cellN.setCellStyle(style4);    232.                         cellN.setCellValue(Double.parseDouble(list.get(k);    233.   

93、                      234.                     if(k>=10 && k<=

94、0;12)    235.                         HSSFCell cellN = rowN.createCell(short)k);    236.       

95、                  cellN.setCellStyle(style5);    237.                       

96、0; cellN.setCellValue(Double.parseDouble(list.get(k);    238.                         239.           

97、0;         240.                 j+;    241.                 242.  

98、0;      catch(Exception e)    243.             e.printStackTrace();    244.         finally    245.  

99、;           if(rs != null)    246.                 try     247.        

100、0;            rs.close();    248.                  catch (SQLException e)     249.    

101、;                 / TODO Auto-generated catch block     250.                    

102、 e.printStackTrace();    251.                     252.                 253.    &

103、#160;        if(sm != null)    254.                 try     255.           

104、          sm.close();    256.                  catch (SQLException e)     257.      &

105、#160;              / TODO Auto-generated catch block     258.                     e.printS

106、tackTrace();    259.                     260.                 261.      

107、60;      if(conn != null)    262.                 try     263.             

108、        conn.close();    264.                  catch (SQLException e)     265.        

109、;             / TODO Auto-generated catch block     266.                     e.printStackTrace(

110、);    267.                     268.                 269.        &

111、#160;    270.             271.         FileOutputStream fileOut = null;    272.         try  &#

112、160;             273.             fileOut = new FileOutputStream("d:workbook.xls");    274.       &#

113、160;     wb.write(fileOut);    275.             /fileOut.close();     276.             System.out.print("OK&qu

114、ot;);    277.         catch(Exception e)    278.             e.printStackTrace();    279.        

115、60;    280.         finally    281.             if(fileOut != null)    282.          

116、       try     283.                     fileOut.close();    284.          &

117、#160;       catch (IOException e)     285.                     / TODO Auto-generated catch block   &

118、#160; 286.                     e.printStackTrace();    287.                    &

119、#160;288.                 289.             290.         291.         292.     293.   </span>  <span style="font-family:Microsoft YaHei;font-size:12px;">import ins.framework.dao.GenericDaoHibernate; import java.io.FileOutputStream; import java.io.