ExcelWriter
1 /**
2 * @author zuzhilong
3 * @date 2013-10-10 下午08:04:02
4 * @desc 生成导出Excel文件对象
5 * @modify
6 * @version 1.0.0
7 */
8 package com.haoyisheng.util;
9
10 import java.io.File;
11 import java.io.FileNotFoundException;
12 import java.io.FileOutputStream;
13 import java.io.IOException;
14 import java.io.OutputStream;
15 import java.util.Calendar;
16
17 import org.apache.poi.hssf.usermodel.HSSFCell;
18 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
19 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
20 import org.apache.poi.hssf.usermodel.HSSFRow;
21 import org.apache.poi.hssf.usermodel.HSSFSheet;
22 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
23
24 public class ExcelWriter {
25 // 设置cell编码解决中文高位字节截断
26 private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
27
28 // 定制浮点数格式
29 private static String NUMBER_FORMAT = "#,##0.00";
30
31 // 定制日期格式
32 private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
33
34 private OutputStream out = null;
35
36 private HSSFWorkbook workbook = null;
37
38 private HSSFSheet sheet = null;
39
40 private HSSFRow row = null;
41
42 public ExcelWriter() {
43 }
44
45 /**
46 * 初始化Excel
47 *
48 */
49 public ExcelWriter(OutputStream out) {
50 this.out = out;
51 this.workbook = new HSSFWorkbook();
52 this.sheet = workbook.createSheet();
53 }
54
55 /**
56 * 导出Excel文件
57 *
58 * @throws IOException
59 */
60 public void export() throws FileNotFoundException, IOException {
61 try {
62 workbook.write(out);
63 out.flush();
64 out.close();
65 } catch (FileNotFoundException e) {
66 throw new IOException(" 生成导出Excel文件出错! ", e);
67 } catch (IOException e) {
68 throw new IOException(" 写入Excel文件出错! ", e);
69 }
70
71 }
72
73 /**
74 * 增加一行
75 *
76 * @param index
77 * 行号
78 */
79 public void createRow(int index) {
80 this.row = this.sheet.createRow(index);
81 }
82
83 /**
84 * 获取单元格的值
85 *
86 * @param index
87 * 列号
88 */
89 public String getCell(int index) {
90 HSSFCell cell = this.row.getCell((short) index);
91 String strExcelCell = "";
92 if (cell != null) { // add this condition
93 // judge
94 switch (cell.getCellType()) {
95 case HSSFCell.CELL_TYPE_FORMULA:
96 strExcelCell = "FORMULA ";
97 break;
98 case HSSFCell.CELL_TYPE_NUMERIC: {
99 strExcelCell = String.valueOf(cell.getNumericCellValue());
100 }
101 break;
102 case HSSFCell.CELL_TYPE_STRING:
103 strExcelCell = cell.getStringCellValue();
104 break;
105 case HSSFCell.CELL_TYPE_BLANK:
106 strExcelCell = "";
107 break;
108 default:
109 strExcelCell = "";
110 break;
111 }
112 }
113 return strExcelCell;
114 }
115
116 /**
117 * 设置单元格
118 *
119 * @param index
120 * 列号
121 * @param value
122 * 单元格填充值
123 */
124 public void setCell(int index, int value) {
125 HSSFCell cell = this.row.createCell((short) index);
126 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
127 cell.setCellValue(value);
128 }
129
130 /**
131 * 设置单元格
132 *
133 * @param index
134 * 列号
135 * @param value
136 * 单元格填充值
137 */
138 public void setCell(int index, double value) {
139 HSSFCell cell = this.row.createCell((short) index);
140 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
141 cell.setCellValue(value);
142 HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
143 HSSFDataFormat format = workbook.createDataFormat();
144 cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
145 cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
146 }
147
148 /**
149 * 设置单元格
150 *
151 * @param index
152 * 列号
153 * @param value
154 * 单元格填充值
155 */
156 public void setCell(int index, String value) {
157 HSSFCell cell = this.row.createCell((short) index);
158 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
159 // cell.setEncoding(XLS_ENCODING);
160 cell.setCellValue(value);
161 }
162
163 /**
164 * 设置单元格
165 *
166 * @param index
167 * 列号
168 * @param value
169 * 单元格填充值
170 */
171 public void setCell(int index, Calendar value) {
172 HSSFCell cell = this.row.createCell((short) index);
173 // cell.setEncoding(XLS_ENCODING);
174 cell.setCellValue(value.getTime());
175 HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
176 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
177 cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
178 }
179
180 public static void main(String[] args) {
181 System.out.println(" 开始导出Excel文件 ");
182
183 File f = new File("d:\\qt.xls");
184 ExcelWriter e = new ExcelWriter();
185
186 try {
187 e = new ExcelWriter(new FileOutputStream(f));
188 } catch (FileNotFoundException e1) {
189 e1.printStackTrace();
190 }
191
192 e.createRow(0);
193 e.setCell(0, "试题编码 ");
194 e.setCell(1, "题型");
195 e.setCell(2, "分值");
196 e.setCell(3, "难度");
197 e.setCell(4, "级别");
198 e.setCell(5, "知识点");
199
200 e.createRow(1);
201 e.setCell(0, "t1");
202 e.setCell(1, 1);
203 e.setCell(2, 3.0);
204 e.setCell(3, 1);
205 e.setCell(4, "重要");
206 e.setCell(5, "专业");
207
208 try {
209 e.export();
210 System.out.println(" 导出Excel文件[成功] ");
211 } catch (IOException ex) {
212 System.out.println(" 导出Excel文件[失败] ");
213 ex.printStackTrace();
214 }
215 }
216
217 }
ExcelReader
1 /**
2 * @author zuzhilong
3 * @date 2013-10-10 下午08:02:22
4 * @desc 读取xls工具类
5 * @modify
6 * @version 1.0.0
7 */
8 package com.haoyisheng.util;
9
10 import java.io.File;
11 import java.io.FileInputStream;
12 import java.io.IOException;
13 import java.text.DecimalFormat;
14 import java.text.SimpleDateFormat;
15 import java.util.Date;
16
17 import org.apache.poi.hssf.usermodel.HSSFCell;
18 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
19 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
20 import org.apache.poi.hssf.usermodel.HSSFRow;
21 import org.apache.poi.hssf.usermodel.HSSFSheet;
22 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
23 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
24 import org.apache.poi.ss.usermodel.CellStyle;
25
26 public class ExcelReader {
27 // 工作薄，也就是一个excel文件
28 private HSSFWorkbook wb = null;// book [includes sheet]
29 //一个excle文件可以有多个sheet
30 private HSSFSheet sheet = null;
31 // 代表了表的第一行，也就是列名
32 private HSSFRow row = null;
33 // 一个excel有多个sheet，这是其中一个
34 private int sheetNum = 0; // 第sheetnum个工作表
35 // 一个sheet中可以有多行，这里应该是给行数的定义
36 private int rowNum = 0;
37 // 文件输入流
38 private FileInputStream fis = null;
39 // 指定文件
40 private File file = null;
41
42 public ExcelReader() {
43 }
44
45 public ExcelReader(File file) {
46 this.file = file;
47 }
48
49 public void setRowNum(int rowNum) {
50 this.rowNum = rowNum;
51 }
52
53 public void setSheetNum(int sheetNum) {
54 this.sheetNum = sheetNum;
55 }
56
57 public void setFile(File file) {
58 this.file = file;
59 }
60
61 /**
62 * 读取excel文件获得HSSFWorkbook对象
63 */
64 public void open() throws IOException {
65 fis = new FileInputStream(file);
66 wb = new HSSFWorkbook(new POIFSFileSystem(fis));
67 fis.close();
68 }
69
70 /**
71 * 返回sheet表数目
72 *
73 * @return int
74 */
75 public int getSheetCount() {
76 int sheetCount = -1;
77 sheetCount = wb.getNumberOfSheets();
78 return sheetCount;
79 }
80
81 /**
82 * sheetNum下的记录行数
83 *
84 * @return int
85 */
86 public int getRowCount() {
87 if (wb == null)
88 System.out.println("=============>WorkBook为空");
89 HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
90 int rowCount = -1;
91 rowCount = sheet.getLastRowNum();
92 return rowCount;
93 }
94
95 /**
96 * 读取指定sheetNum的rowCount
97 *
98 * @param sheetNum
99 * @return int
100 */
101 public int getRowCount(int sheetNum) {
102 HSSFSheet sheet = wb.getSheetAt(sheetNum);
103 int rowCount = -1;
104 rowCount = sheet.getLastRowNum();
105 return rowCount;
106 }
107
108 /**
109 * 得到指定行的内容
110 *
111 * @param lineNum
112 * @return String[]
113 */
114 public String[] readExcelLine(int lineNum) {
115 return readExcelLine(this.sheetNum, lineNum);
116 }
117
118 /**
119 * 指定工作表和行数的内容
120 *
121 * @param sheetNum
122 * @param lineNum
123 * @return String[]
124 */
125 public String[] readExcelLine(int sheetNum, int lineNum) {
126 if (sheetNum < 0 || lineNum < 0)
127 return null;
128 String[] strExcelLine = null;
129 try {
130 sheet = wb.getSheetAt(sheetNum);
131 row = sheet.getRow(lineNum);
132
133 int cellCount = row.getLastCellNum();
134 strExcelLine = new String[cellCount + 1];
135 for (int i = 0; i <= cellCount; i++) {
136 strExcelLine[i] = readStringExcelCell(lineNum, i);
137 }
138 } catch (Exception e) {
139 e.printStackTrace();
140 }
141 return strExcelLine;
142 }
143
144 /**
145 * 读取指定列的内容
146 *
147 * @param cellNum
148 * @return String
149 */
150 public String readStringExcelCell(int cellNum) {
151 return readStringExcelCell(this.rowNum, cellNum);
152 }
153
154 /**
155 * 指定行和列编号的内容
156 *
157 * @param rowNum
158 * @param cellNum
159 * @return String
160 */
161 public String readStringExcelCell(int rowNum, int cellNum) {
162 return readStringExcelCell(this.sheetNum, rowNum, cellNum);
163 }
164
165 /**
166 * 指定工作表、行、列下的内容
167 *
168 * @param sheetNum
169 * @param rowNum
170 * @param cellNum
171 * @return String
172 */
173 public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
174 if (sheetNum < 0 || rowNum < 0)
175 return "";
176 String strExcelCell = "";
177 try {
178 sheet = wb.getSheetAt(sheetNum);
179 row = sheet.getRow(rowNum);
180
181 if (row.getCell((short) cellNum) != null) { // add this condition
182 // judge
183 switch (row.getCell((short) cellNum).getCellType()) {
184 case HSSFCell.CELL_TYPE_FORMULA:
185 strExcelCell = "FORMULA ";
186 break;
187 case HSSFCell.CELL_TYPE_NUMERIC: {
188 if (HSSFDateUtil.isCellDateFormatted(row.getCell((short) cellNum))) {// 处理日期格式、时间格式
189 SimpleDateFormat sdf = null;
190 if (row.getCell((short) cellNum).getCellStyle().getDataFormat() == HSSFDataFormat
191 .getBuiltinFormat("h:mm")) {
192 sdf = new SimpleDateFormat("HH:mm");
193 } else {// 日期
194 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
195 }
196 Date date = row.getCell((short) cellNum).getDateCellValue();
197 strExcelCell = sdf.format(date);
198 } else if (row.getCell((short) cellNum).getCellStyle().getDataFormat() == 58) {
199 // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
200 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
201 double value = row.getCell((short) cellNum).getNumericCellValue();
202 Date date = org.apache.poi.ss.usermodel.DateUtil
203 .getJavaDate(value);
204 strExcelCell = sdf.format(date);
205 } else {
206 double value = row.getCell((short) cellNum).getNumericCellValue();
207 CellStyle style = row.getCell((short) cellNum).getCellStyle();
208 DecimalFormat format = new DecimalFormat("0.0");
209 String temp = style.getDataFormatString();
210 // 单元格设置成常规
211 if (temp.equals("General")) {
212 format.applyPattern("#.#");
213 }
214 strExcelCell = format.format(value);
215 }
216 }
217 break;
218 case HSSFCell.CELL_TYPE_STRING:
219 strExcelCell = row.getCell((short) cellNum)
220 .getStringCellValue();
221 break;
222 case HSSFCell.CELL_TYPE_BLANK:
223 strExcelCell = "";
224 break;
225 default:
226 strExcelCell = "";
227 break;
228 }
229 }
230 } catch (Exception e) {
231 e.printStackTrace();
232 }
233 return strExcelCell;
234 }
235
236 public static void main(String args[]) {
237 File file = new File("d:\\无锡妇幼保健人员对应培训项目确认表(返）.xls");
238 ExcelReader readExcel = new ExcelReader(file);
239 try {
240 readExcel.open();
241 } catch (IOException e) {
242 e.printStackTrace();
243 }
244 readExcel.setSheetNum(0); // 设置读取索引为0的工作表
245 // 总行数
246 int count = readExcel.getRowCount();
247 for (int i = 0; i <= count; i++) {
248 String[] rows = readExcel.readExcelLine(i);
249 for (int j = 0; j < rows.length; j++) {
250 System.out.print(rows[j] + " ");
251 }
252 System.out.print("\n");
253 }
254 }
255 }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
