Excel文件office2003 and office2007 操作

最新推荐文章于 2022-09-21 17:49:58 发布

阿★永

最新推荐文章于 2022-09-21 17:49:58 发布

阅读量365

点赞数

分类专栏： J2EE

本文链接：https://blog.csdn.net/lsy0903/article/details/52512797

版权

J2EE 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

这篇blog主要是讲述java中poi读取excel，而excel的版本包括：2003-2007和2010两个版本，即excel的后缀名为：xls和xlsx。

poi-3.7-20101029.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107089
geronimo-stax-api_1.0_spec-1.0.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107083
xmlbeans-2.3.0.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107140
poi-ooxml-3.7-20101029.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107145
poi-ooxml-schemas-3.7-20101029.jar，下载地址：http://download.csdn.net/detail/evangel_z/4108997
以上5个jar，就可读取Excel 2003；
-----------------------------------------------------------------------------------------------------------------------
读取Excel 2007，请加上dom4j-1.6.1.jar，下载地址：http://download.csdn.net/detail/evangel_z/6739735
-----------------------------------------------------------------------------------------------------------------------
poi-3.6-20091214.jar，下载地址：http://download.csdn.net/detail/evangel_z/3895051
poi-contrib-3.6-20091214.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107197
poi-scratch.6-20091214.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107204

=================================================

源码部分：读取操作 ReadExcelUtil

=================================================

  1 import java.io.File;
  2 import java.io.FileInputStream;
  3 import java.io.FileNotFoundException;
  4 import java.io.IOException;
  5 import java.text.DecimalFormat;
  6 import java.text.SimpleDateFormat;
  7 import java.util.ArrayList;
  8 import java.util.LinkedList;
  9 import java.util.List;
 10 
 11 import org.apache.poi.hssf.usermodel.*;
 12 import org.apache.poi.xssf.usermodel.XSSFCell;
 13 import org.apache.poi.xssf.usermodel.XSSFRow;
 14 import org.apache.poi.xssf.usermodel.XSSFSheet;
 15 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 16 
 17 /**
 18  * 读Excel文件office2003 and office2007
 19  * @author lerry.li
 20  * @date 2016-9-2
 21  */
 22 public class ReadExcelUtil {
 23 
 24     private static final org.slf4j.Logger log = org.slf4j.LoggerFactory.getLogger(ReadExcelUtil.class);
 25     static DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
 26     static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
 27     static DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
 28 
 29     /**
 30      * 对外提供读取excel 的方法
 31      * @param file
 32      * @return
 33      * @throws java.io.IOException
 34      * @throws java.io.IOException
 35      */
 36     public static List<List<Object>> readExcel(File file) throws IOException {
 37         String fileName = file.getName();
 38         String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
 39                 .substring(fileName.lastIndexOf(".") + 1);
 40         if ("xls".equals(extension)) {
 41             return read2003Excel(file);
 42         } else if ("xlsx".equals(extension)) {
 43             return read2007Excel(file);
 44         } else {
 45             throw new IOException("不支持的文件类型");
 46         }
 47     }
 48 
 49     /**
 50      * 读取 office 2003 excel
 51      * @throws java.io.IOException
 52      * @throws java.io.FileNotFoundException
 53      */
 54     @SuppressWarnings("unused")
 55     private static List<List<Object>> read2003Excel(File file)
 56             throws IOException {
 57         List<List<Object>> list = new LinkedList<List<Object>>();
 58         HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
 59         HSSFSheet sheet = hwb.getSheetAt(0);
 60         Object value = null;
 61         HSSFRow row = null;
 62         HSSFCell cell = null;
 63         for (int i = sheet.getFirstRowNum(); i <= sheet
 64                 .getPhysicalNumberOfRows(); i++) {
 65             row = sheet.getRow(i);
 66             if (row == null) {
 67                 continue;
 68             }
 69             List<Object> linked = new LinkedList<Object>();
 70             for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
 71                 cell = row.getCell(j);
 72                 if (cell == null) {
 73                     continue;
 74                 }
 75                 DecimalFormat df = new DecimalFormat("0");// 格式化 number String
 76                 // 字符
 77                 SimpleDateFormat sdf = new SimpleDateFormat(
 78                         "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
 79                 DecimalFormat nf = new DecimalFormat("0");// 格式化数字
 80                 switch (cell.getCellType()) {
 81                 case HSSFCell.CELL_TYPE_STRING:
 82                     value = cell.getStringCellValue();
 83                     break;
 84                 case HSSFCell.CELL_TYPE_NUMERIC:
 85                     if ("@".equals(cell.getCellStyle().getDataFormatString())) {
 86                         value = df.format(cell.getNumericCellValue());
 87                     } else if ("General".equals(cell.getCellStyle()
 88                             .getDataFormatString())) {
 89                         value = nf.format(cell.getNumericCellValue());
 90                     } else {
 91                         value = sdf.format(HSSFDateUtil.getJavaDate(cell
 92                                 .getNumericCellValue()));
 93                     }
 94                     break;
 95                 case HSSFCell.CELL_TYPE_BOOLEAN:
 96                     value = cell.getBooleanCellValue();
 97                     break;
 98                 case HSSFCell.CELL_TYPE_BLANK:
 99                     value = "";
100                     break;
101                 default:
102                     value = cell.toString();
103                 }
104                 //if (value == null || "".equals(value)) {
105                 //    continue;
106                 //}
107                 if (value == null) {
108                     continue;
109                 }
110                 linked.add(value);
111             }
112             list.add(linked);
113         }
114         return list;
115     }
116 
117     /**
118      * 读取Office 2007 excel
119      * @param file
120      * @return
121      * @throws java.io.IOException
122      */
123     private static List<List<Object>> read2007Excel(File file)
124             throws IOException {
125         int flag=0;
126         List<List<Object>> list = new ArrayList<List<Object>>();
127         // 构造 XSSFWorkbook 对象，strPath 传入文件路径
128         XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
129         // 读取第一章表格内容
130         XSSFSheet sheet = xwb.getSheetAt(0);
131         Object value = null;
132         XSSFRow row = null;
133         XSSFCell cell = null;
134         for (int i = sheet.getFirstRowNum(); i <= sheet
135                 .getPhysicalNumberOfRows(); i++) {
136             flag++;
137             row = sheet.getRow(i);
138             if (row == null) {
139                 continue;
140             }
141             List<Object> linked = new LinkedList<Object>();
142             for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
143                 cell = row.getCell(j);
144                 if (cell == null) {
145                     continue;
146                 }
147                 //DecimalFormat df = new DecimalFormat("0");// 格式化 number String
148                 // 字符
149                 switch (cell.getCellType()) {
150                 case HSSFCell.CELL_TYPE_STRING:
151                     value = cell.getStringCellValue();
152                     break;
153                 case HSSFCell.CELL_TYPE_NUMERIC:
154                     if ("@".equals(cell.getCellStyle().getDataFormatString())) {
155                         value = df.format(cell.getNumericCellValue());
156                     } else if ("General".equals(cell.getCellStyle()
157                             .getDataFormatString())) {
158                         value = nf.format(cell.getNumericCellValue());
159                     } else {
160                         value = sdf.format(HSSFDateUtil.getJavaDate(cell
161                                 .getNumericCellValue()));
162                     }
163                     break;
164                 case HSSFCell.CELL_TYPE_BOOLEAN:
165                     value = cell.getBooleanCellValue();
166                     break;
167                 case HSSFCell.CELL_TYPE_BLANK:
168                     value = "";
169                     break;
170                 default:
171                     value = cell.toString();
172                 }
173                 //if (value == null || "".equals(value)) {
174                 //    continue;
175                 //}
176                 if (value == null) {
177                     continue;
178                 }
179                 linked.add(value);
180             }
181             list.add(linked);
182         }
183         return list;
184     }
185     /**
186      * 获取行数2003
187      * @param file
188      * @return 行数
189      */
190     public static int getRow2003(File file){
191         HSSFWorkbook hwb = null;
192         try {
193             FileInputStream f =  new FileInputStream(file);
194             hwb = new HSSFWorkbook(f);
195         } catch (FileNotFoundException e) {
196             e.printStackTrace();
197         } catch (IOException e) {
198             e.printStackTrace();
199         }
200         HSSFSheet sheet = hwb.getSheetAt(0);
201         return sheet.getPhysicalNumberOfRows();
202     }
203     /**
204      * 获取行数2007
205      * @param file
206      * @return
207      */
208     public static int getRow2007(File file){
209         XSSFWorkbook xwb = null;
210         try {
211             FileInputStream f = new FileInputStream(file);
212             xwb = new XSSFWorkbook(f);
213         } catch (Exception e) {
214             e.printStackTrace();
215         }
216         XSSFSheet sheet = xwb.getSheetAt(0);
217         return sheet.getPhysicalNumberOfRows();
218     }
219     
220     public static void main(String[] args) {
221         
222         File f = new File("C:/Users/Administrator/Desktop/Book1.xlsx");
223         try {
224             List<List<Object>> list = readExcel(f);
225             for (int i = 0; i < list.size(); i++) {
226                 String s1 = list.get(i).toString().trim();
227                 s1 = s1.replace("[", "");
228                 s1 = s1.replace("]", "");
229                 s1 = s1.replace("  ", ""); 
230                 String str = s1.replaceAll(" ", "").trim();
231                 String[] s=str.split(",");
232                 for(int j=0;j<s.length;j++){
233                     System.out.println("==="+s[j]);
234                 }
235             }
236         } catch (Exception e) {
237             e.printStackTrace();
238         }
239     }
240 
241 }

=================================================

源码部分：下载操作 DownLoadExcelUtil

=================================================

 1 import java.io.BufferedInputStream;
 2 import java.io.BufferedOutputStream;
 3 import java.io.File;
 4 import java.io.FileInputStream;
 5 import java.io.IOException;
 6 import java.io.InputStream;
 7 import java.io.OutputStream;
 8 
 9 import javax.servlet.ServletContext;
10 import javax.servlet.ServletOutputStream;
11 import javax.servlet.http.HttpServletRequest;
12 import javax.servlet.http.HttpServletResponse;
13 
14 import org.slf4j.Logger;
15 
16 /**
17  * 读Excel文件office2003 and office2007
18  * @author lerry.li
19  * @date 2016-9-2
20  */
21 public class DownLoadExcelUtil {
22 
23     private static final Logger log = org.slf4j.LoggerFactory.getLogger(DownLoadExcelUtil.class);
24     /**
25      * 下载Excel
26      * @param path 文件路径
27      * @param response
28      */
29     public static void download(String path, HttpServletResponse response) {  
30         try {  
31             // path是指欲下载的文件的路径。  
32             File file = new File(path);  
33             // 取得文件名。  
34             String filename = file.getName();  
35             // 以流的形式下载文件。  
36             InputStream fis = new BufferedInputStream(new FileInputStream(path));  
37             byte[] buffer = new byte[fis.available()];  
38             fis.read(buffer);  
39             fis.close();  
40             // 清空response  
41             response.reset();  
42             // 设置response的Header  
43             response.addHeader("Content-Disposition", "attachment;filename="  
44                     + new String(filename.getBytes()));  
45             response.addHeader("Content-Length", "" + file.length());  
46             OutputStream toClient = new BufferedOutputStream(  
47                     response.getOutputStream());  
48             response.setContentType("application/vnd.ms-excel;charset=gb2312");  
49             toClient.write(buffer);  
50             toClient.flush();  
51             toClient.close();  
52         } catch (Exception ex) {  
53             log.info(ex.getMessage());
54             ex.printStackTrace();  
55         }  
56     } 
57     
58 }