工具類：
1
package ash_ljv2.framework.util;
2import java.io.*;
3import java.util.Date;
4import java.util.UUID;
5import jxl.Cell;
6import jxl.Sheet;
7import jxl.Workbook;
8import jxl.format.UnderlineStyle;
9import jxl.read.biff.BiffException;
10import jxl.write.*;
11import jxl.write.Number;
12import jxl.write.Boolean;
13
public class Excel
{
14
//文件路徑
15private String path;
16private String tableName ;
17private String[] tableCols;
18//工作薄集合
19private Workbook workbook;
20
public Excel(String path,String tableName,String[] tableCols) throws BiffException,IOException{
21 this.tableName = tableName;
22 this.tableCols = tableCols;
23 this.setPath(path);
24 this.setWorkbook(Workbook.getWorkbook(new java.io.File(path)));
25
}
26/** *//**
27 * 獲取工作薄數量
28 * @return 工作薄數量
29 */
30public int getNumberOfSheets(Workbook book){
31 return book == null ? 0 :book.getNumberOfSheets();
32}
33/** *//**
34 * 獲取工作薄總行數
35 * @param sheet 工作薄
36 * @return 工作薄總行數
37 */
38public int getRows(Sheet sheet){
39 return sheet == null ? 0 : sheet.getRows();
40}
41/** *//**
42 * 獲取最大列數
43 * @param sheet 工作薄
44 * @return 總行數最大列數
45 */
46public int getColumns(Sheet sheet){
47 return sheet == null ? 0 : sheet.getColumns();
48}
49/** *//**
50 * 獲取每行單元格數組
51 * @param sheet 工作薄
52 * @param row 行數
53 * @return 每行單元格數組
54 */
55public Cell[] getRows(Sheet sheet,int row){
56 return sheet == null || sheet.getRows() < row ? null : sheet.getRow(row);
57}
58/** *//**
59 * 獲取每行單元格數組
60 * @param sheet 工作薄
61 * @param endrow 結束行
62 * @param endCol 結束列
63 * @return 每行單元格數組
64 */
65public Cell[][] getCells(Sheet sheet,int endrow,int endcol){
66 return getCells(sheet,0,endrow,0,endcol);
67}
68/** *//**
69 * 獲取每行單元格數組
70 * @param sheet 工作薄
71 * @param startrow 行數
72 * @param endrow 結束行
73 * @param startcol 開始列
74 * @param endCol 結束列
75 * @return 每行單元格數組
76 */
77public Cell[][] getCells(Sheet sheet,int startrow,int endrow,int startcol,int endcol) {
78 Cell[][] cellArray = new Cell[endrow-startrow][endcol-startcol];
79 int maxRow = this.getRows(sheet);
80 int maxCos = this.getColumns(sheet);
81 for(int i = startrow ;i < endrow && i < maxRow ; i++){
82
83 for(int j = startcol ; j < endcol && j < maxCos ; j++ ){
84
85 cellArray[i-startrow][j-startcol] = sheet.getCell(j, i);
86 }
87
88 }
89 return cellArray;
90}
91/** *//**
92 * 得到行的值
93 * @param sheet
94 * @param col
95 * @param startrow
96 * @param endrow
97 * @return
98 */
99public Cell[] getColCells(Sheet sheet,int col,int startrow,int endrow){
100 Cell[] cellArray = new Cell[endrow-startrow];
101 int maxRow = this.getRows(sheet);
102 int maxCos = this.getColumns(sheet);
103 if(col <= 0 || col > maxCos || startrow > maxRow || endrow < startrow){
104 return null;
105 }
106 if(startrow < 0){
107 startrow = 0;
108 }
109 for(int i = startrow ;i < endrow && i < maxRow ; i++){
110 cellArray[i-startrow] = sheet.getCell(col,i);
111 }
112 return cellArray;
113}
114/** *//**
115 * 得到列的值
116 * @param sheet
117 * @param row
118 * @param startcol
119 * @param endcol
120 * @return
121 */
122public Cell[] getRowCells(Sheet sheet,int row,int startcol,int endcol){
123 Cell[] cellArray = new Cell[endcol-startcol];
124 int maxRow = this.getRows(sheet);
125 int maxCos = this.getColumns(sheet);
126 if(row <= 0 || row > maxRow || startcol > maxCos || endcol < startcol){
127 return null;
128 }
129 if(startcol < 0){
130 startcol = 0;
131 }
132 for(int i = startcol ;i < startcol && i < maxCos ; i++){
133 cellArray[i-startcol] = sheet.getCell(i,row);
134 }
135 return cellArray;
136}
137
138/** *//**
139 * 生成隨機ID
140 * @return
141 */
142public static String getStrRandomId(){
143 String uuid = UUID.randomUUID().toString().replace("-","");
144 return uuid;
145}
146/** *//**
147 * 組裝SQL語句(擴展導入數據庫額外增加字段的情況)
148 * @param sheet 工作薄
149 * @param startrow 開始行
150 * @param endrow 結束行
151 * @param startcol 開始列
152 * @param endcol 結束列
153 * @return SQL語句數組
154 */
155public Object[] constrctCellsSql(Sheet sheet,int startrow,int endrow,int startcol,int endcol,String payTime){
156 Cell[][] cellArray = getCells(sheet, startrow, endrow,startcol,endcol);
157 java.util.ArrayList<String> list = new java.util.ArrayList<String>();
158 StringBuffer bf = new StringBuffer("INSERT INTO " + tableName+"(ID,");
159 for(int i = 0 ; tableCols != null && i < tableCols.length ; i++){
160 if(i != tableCols.length -1)
161 bf.append(tableCols[i]).append(",");
162 else
163 bf.append(tableCols[i]).append("");
164
165 }
166 bf.append(",PAY_TIME) VALUES ");
167 for(int i = 0;i< cellArray.length;i++){
168 //在第一列前加個隨機數列
169 StringBuffer sqlBuffer = new StringBuffer();
170 sqlBuffer.append(bf.toString()+"('"+getStrRandomId()+"',");
171 Cell[] cell = cellArray[i];
172 if(tableCols != null && cell != null && tableCols.length != cell.length)
173 continue;
174 for(int j = 0 ; j < cell.length; j++){
175 String tmp = "";
176 if(cell[j] != null && cell[j].getContents() != null){
177 tmp = (String)cell[j].getContents();
178 }
179 if(j != cell.length -1 )
180 sqlBuffer.append("'").append(tmp).append("',");
181 else
182 sqlBuffer.append("'").append(tmp).append("'");
183 }
184 //增加時間字段
185 sqlBuffer.append(",").append("to_date('"+payTime+"','YYYY-MM-DD HH24:MI:SS')");
186 sqlBuffer.append(")");
187 list.add(sqlBuffer.toString());
188 System.out.println(sqlBuffer.toString());
189 }
190 System.out.println(list);
191 return list.toArray();
192}
193/** *//**
194 * 獲取Excel文件路徑
195 * @return Excel文件路徑
196 */
197public String getPath(){
198 return this.path;
199}
200/** *//**
201 * 設置Excel文件路徑
202 * @param path Excel文件路徑
203 */
204public void setPath(String path){
205 this.path = path;
206}
207/** *//**
208 * 獲取工作薄集合
209 */
210public Workbook getWorkbook(){
211 return this.workbook;
212}
213/** *//**
214 * 設置工作薄集合
215 * @param workbook 工作薄集合
216 */
217public void setWorkbook(Workbook workbook){
218 this.workbook = workbook;
219}
220/** *//**
221 *
222 * @param args
223 */
224public static void main(String[] args){
225 try {
226 File fileWrite = new File("c:/testWrite.xls");
227 fileWrite.createNewFile();
228 OutputStream os = new FileOutputStream(fileWrite);
229 Excel.writeExcel(os);
230 } catch (IOException e) {
231 // TODO Auto-generated catch block
232 e.printStackTrace();
233 }
234}
235
}
236
-------------------------
讀取類：
1package cn.doc.service.impl;
2import java.io.IOException;
3import java.util.ArrayList;
4import java.util.List;
5import javax.servlet.ServletContext;
6import jxl.Cell;
7import jxl.Sheet;
8import jxl.Workbook;
9import jxl.read.biff.BiffException;
10import com.opensymphony.xwork2.ActionContext;
11import pojo.TblTableTemplate;
12import ash_ljv2.framework.util.Excel;
13import ash_ljv2.framework.util.PageBean;
14import cn.doc.dao.TableTemplateDao;
15import cn.doc.service.TableTemplateService;
16public class TableTemplateServiceImpl implements TableTemplateService{
17private TableTemplateDao tableTemplateDao;
18public TableTemplateDao getTableTemplateDao() {
19 return tableTemplateDao;
20}
21public void setTableTemplateDao(TableTemplateDao tableTemplateDao) {
22 this.tableTemplateDao = tableTemplateDao;
23}
24/** *//**
25 * 讀取excel
26 * @return
27 */
28public List importTableTemplate(String path){
29 ArrayList list=new ArrayList();
30 ServletContext request = (ServletContext) ActionContext.getContext()
31 .get("com.opensymphony.xwork2.dispatcher.ServletContext");
32 try {
33 Excel excel = new Excel(request.getRealPath(path),null,null);
34 Workbook workbook = excel.getWorkbook();
35 Sheet sheet = workbook.getSheet(0);
36 int a = excel.getRows(sheet); //最大行數
37 int m=excel.getColumns(sheet); //最大列數
38 Cell[][] c = excel.getCells(sheet,0,a,0,m);
39 String f1 = null,f3=null;
40 for(int i =0 ; i < c.length;i++){
41 Cell[] obj = c[i];
42 for(int j =0 ;j< obj.length; j++ ){
43 f1=obj[j].getContents().toString();
44 list.add(f1);
45 }
46 }
47 } catch (Exception e) {
48 e.printStackTrace();
49 }
50 return list;
51}
52}
53
--------------------
導入數據庫類：
1Excel excel=null;
2Workbook workbook=null;
3Connection conn = session.connection();
4ServletContext request = (ServletContext) ActionContext.getContext().get("com.opensymphony.xwork2.dispatcher.ServletContext");
5try {
6excel = new Excel(request.getRealPath(path),"tbl_name",new String[]{"NAME","TYPE"}); //數組中為字段名
7}catch(Exception e) {
8//e.printStackTrace();
9}
10workbook = excel.getWorkbook();
11Sheet sheet = workbook.getSheet(0);
12Object[] obj=excel.constrctCellsSql(sheet,1,excel.getRows(sheet),0,excel.getColumns(sheet),payTime); //payTime為工具類中額外加的字段
13//這里做些業務邏輯判斷
14for(int i=0;i<obj.length;i++){
15Statement stmt;
16try {
17 stmt = conn.createStatement();
18 stmt.execute(obj[i].toString());
19 stmt.close();
20} catch (SQLException e) {
21 throw new AppException("導入的文件數據格式不正確！");
22}
23}
24
25