Java POI操作Excle工具类
用到了jxl.jar和poi.jar
一些基本的操作Excel的操作方法:
1 import java.io.File;2 import java.io.FileInputStream;
3 import java.io.FileOutputStream;
4 import java.io.IOException;
5 import java.io.InputStream;
6 import java.util.ArrayList;
7 import java.util.List;
8
9 import jxl.Cell;
10 import jxl.Workbook;
11
12 import org.apache.log4j.Logger;
13 import org.apache.poi.hssf.usermodel.HSSFCell;
14 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
15 import org.apache.poi.hssf.usermodel.HSSFFont;
16 import org.apache.poi.hssf.usermodel.HSSFRow;
17 import org.apache.poi.hssf.usermodel.HSSFSheet;
18 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
19 import org.apache.poi.hssf.util.HSSFColor;
20 import org.dom4j.Document;
21 import org.dom4j.DocumentException;
22
23 import com.chinadigitalvideo.hibernate.Provider;
24 import com.chinadigitalvideo.service.ProviderMgr;
25 import com.chinadigitalvideo.utils.bean.App_Data;
26 import com.chinadigitalvideo.xbase.GUID;
27
28 public class POIExcelHelper {
29 public static Logger logger = Logger.getLogger(POIExcelHelper.class);
30
31 /**
32 * 设置表头样式
33 * @param workbook
34 * @return
35 */
36 public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
37 HSSFFont font = workbook.createFont();
38 font.setColor(HSSFColor.BLUE.index);
39 font.setFontHeight((short) 200);
40 font.setFontName("楷体_GB2312");
41 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
42
43 HSSFCellStyle style = workbook.createCellStyle();
44 style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
45 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
46 style.setFont(font);
47 style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
48 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
49 return style;
50 }
51
52 /**
53 * 设置表格特别数据样式
54 * @param workbook
55 * @return
56 */
57 public static HSSFCellStyle getDataStyle2(HSSFWorkbook workbook) {
58 HSSFFont font = workbook.createFont();
59 font.setColor(HSSFColor.BLACK.index);
60 font.setFontHeight((short) 200);
61 font.setFontName("楷体_GB2312");
62
63 HSSFCellStyle style = workbook.createCellStyle();
64 style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
65 style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
66 style.setFont(font);
67 style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
68 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
69 return style;
70 }
71
72 /**
73 * 创建单元格内容
74 * @param row
75 * @param id
76 * @param value
77 * @param style
78 */
79 @SuppressWarnings("deprecation")
80 public static void createCell(HSSFRow row, int id, String value, HSSFCellStyle style) {
81 HSSFCell cell = row.createCell((short) id);
82 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
83 cell.setCellValue(value);
84 if (style != null) {
85 cell.setCellStyle(style);
86 }
87 }
88
89 /**
90 * 创建报表文件
91 * @param workbook
92 * @param dir
93 * @param filename
94 * @throws IOException
95 */
96 public static void createFile(HSSFWorkbook workbook, String dir, String filename)
97 throws IOException {
98 dir = dir == null ? "" : dir.trim();
99 if( !"".equals(dir) ){
100 if( !dir.endsWith(File.separator) ){
101 dir += File.separator ;
102 }
103 }
104 logger.debug("out put dir: " + dir);
105 File outdir = new File(dir);
106 if (!outdir.exists()) {
107 outdir.mkdirs();
108 }
109 FileOutputStream fOut = new FileOutputStream(dir + filename);
110 workbook.write(fOut);
111 fOut.flush();
112 fOut.close();
113 logger.info(dir + filename + "已经生成!");
114 }
115
116 /**
117 * 读取Excel中所有的列
118 * @param filename
119 * @return
120 * @throws IOException
121 */
122 private static List<Cell[]> jxlGetExcelColumns(String filename) throws IOException {
123 InputStream is = null;
124 jxl.Workbook rwb = null;
125 List<Cell[]> list = new ArrayList<Cell[]>();
126 try {
127 is = new FileInputStream(filename);
128 rwb = Workbook.getWorkbook(is);
129 // Sheet[] sheets = rwb.getSheets();
130 // int sheetLen = sheets.length;
131 jxl.Sheet rs = rwb.getSheet(0); // 读取第一个工作表的数据
132
133 //getRows() 获取总共多少列...getColumn(n)获取第n列...
134 for(int i=0; i<rs.getColumns(); i++ ){
135 list.add(rs.getColumn(i));
136 }
137 } catch (Exception ex) {
138 ex.printStackTrace();
139 } finally {
140 rwb.close();
141 is.close();
142 }
143 return list;
144 }
145
146 /**
147 * 读取Excel中所有的行
148 * @param filename
149 * @return
150 */
151 private static List<Cell[]> jxlGetExcelRows(String filename) {
152 InputStream is = null;
153 jxl.Workbook rwb = null;
154 List<Cell[]> list = new ArrayList<Cell[]>();
155 try {
156 is = new FileInputStream(filename);
157 rwb = Workbook.getWorkbook(is);
158 // Sheet[] sheets = rwb.getSheets();
159 // int sheetLen = sheets.length;
160 jxl.Sheet rs = rwb.getSheet(0); // 默认先读取第一个工作表的数据
161
162 //getRows() 获取总共多少行...getRow(n)获取第n行...
163 for(int i=0; i<rs.getRows(); i++ ){
164 list.add(rs.getRow(i));
165 }
166 } catch (Exception ex) {
167 ex.printStackTrace();
168 } finally {
169 rwb.close();
170 try {
171 is.close();
172 } catch (IOException e) {
173 e.printStackTrace();
174 }
175 }
176 return list;
177 }
178 }
以上是 Java POI操作Excle工具类 的全部内容， 来源链接： utcz.com/z/394585.html
