exceljava
通過數字查詢？是在結果集 通過列的順序查詢不是 可以用數字指定列 也可以用那列的名進行查詢 下面這個例子
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
public static void main(String args[]){
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:testExcel","","");
Statement sta = con.createStatement();
ResultSet rs = sta.executeQuery("select * from [sheet1$]");
System.out.println("測試");
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("age"));
}
}catch(Exception e){
e.printStackTrace();
}
}
}
Ⅱ excel往java中導入怎麼解決
1、加入依賴的jar文件：
1
2
3
引用：
*mysql的jar文件
*Spring_HOME/lib/poi/*.jar
2、編寫資料庫鏈接類
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package com.zzg.db;
import java.sql.Connection;
import java.sql.DriverManager;
public class DbUtils {
private static Connection conn;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConn() {
return conn;
}
public static void setConn(Connection conn) {
DbUtils.conn = conn;
}
}
3、編寫資料庫操作類
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.zzg.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class ExcuteData {
private PreparedStatement pstmt;
public boolean ExcuData(String sql) {
Connection conn = DbUtils.getConn();
boolean flag=false;
try {
pstmt = conn.prepareStatement(sql);
flag=pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}
}
4、編寫Excel表格實體類
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.zzg.model;
public class TableCell {
private String _name;
private String _value;
public String get_name() {
return _name;
}
public void set_name(String _name) {
this._name = _name;
}
public String get_value() {
return _value;
}
public void set_value(String _value) {
this._value = _value;
}
}
5、編寫主鍵生成方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.zzg.util;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
public class GenericUtil {
public static String getPrimaryKey()
{
String primaryKey;
primaryKey = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
Random r = new Random();
primaryKey +=r.nextInt(100000)+100000;
return primaryKey;
}
}
6、編寫Excel操作類
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
package com.zzg.deployData;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.zzg.db.ExcuteData;
import com.zzg.model.TableCell;
import com.zzg.util.GenericUtil;
public class OperExcel<T extends Serializable> {
private HSSFWorkbook workbook;
private String tableName;
private Class<T> type;
private String sheetName;
public OperExcel(File excelFile, String tableName, Class<T> type,
String sheetName) throws FileNotFoundException,
IOException {
workbook = new HSSFWorkbook(new FileInputStream(excelFile));
this.tableName = tableName;
this.type = type;
this.sheetName = sheetName;
InsertData();
}
// 向表中寫入數據
public void InsertData() {
System.out.println("yyy");
ExcuteData excuteData = new ExcuteData();
List<List> datas = getDatasInSheet(this.sheetName);
// 向表中添加數據之前先刪除表中數據
String strSql = "delete from " + this.tableName;
excuteData.ExcuData(strSql);
// 拼接sql語句
for (int i = 1; i < datas.size(); i++) {
strSql = "insert into " + this.tableName + "(";
List row = datas.get(i);
for (short n = 0; n < row.size(); n++) {
TableCell excel = (TableCell) row.get(n);
if (n != row.size() - 1)
strSql += excel.get_name() + ",";
else
strSql += excel.get_name() + ")";
}
strSql += " values (";
for (short n = 0; n < row.size(); n++) {
TableCell excel = (TableCell) row.get(n);
try {
if (n != row.size() - 1) {
strSql += getTypeChangeValue(excel) + ",";
} else
strSql += getTypeChangeValue(excel) + ")";
} catch (RuntimeException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//執行sql
excuteData.ExcuData(strSql);
}
}
/**
* 獲得表中的數據
* @param sheetName 表格索引(EXCEL 是多表文檔,所以需要輸入表索引號)
* @return 由LIST構成的行和表
*/
public List<List> getDatasInSheet(String sheetName) {
List<List> result = new ArrayList<List>();
// 獲得指定的表
HSSFSheet sheet = workbook.getSheet(sheetName);
// 獲得數據總行數
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {
return result;
}
// 逐行讀取數據
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
// 獲得行對象
HSSFRow row = sheet.getRow(rowIndex);
if (row != null) {
List<TableCell> rowData = new ArrayList<TableCell>();
// 獲得本行中單元格的個數
int columnCount = sheet.getRow(0).getLastCellNum();
// 獲得本行中各單元格中的數據
for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) {
HSSFCell cell = row.getCell(columnIndex);
// 獲得指定單元格中數據
Object cellStr = this.getCellString(cell);
TableCell TableCell = new TableCell();
TableCell.set_name(getCellString(
sheet.getRow(0).getCell(columnIndex)).toString());
TableCell.set_value(cellStr == null ? "" : cellStr
.toString());
rowData.add(TableCell);
}
result.add(rowData);
}
}
return result;
}
/**
* 獲得單元格中的內容
*
* @param cell
* @return result
*/
protected Object getCellString(HSSFCell cell) {
Object result = null;
if (cell != null) {
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR:
result = null;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
}
}
return result;
}
// 根據類型返回相應的值
@SuppressWarnings("unchecked")
public String getTypeChangeValue(TableCell excelElement)
throws RuntimeException, Exception {
String colName = excelElement.get_name();
String colValue = excelElement.get_value();
String retValue = "";
if (colName.equals("id")) {
retValue = "'" + GenericUtil.getPrimaryKey() + "'";
return retValue;
}
if (colName == null) {
retValue = null;
}
if (colName.equals("class_createuser")) {
retValue = "yaa101";
return "'" + retValue + "'";
}
retValue = "'" + colValue + "'";
return retValue;
}
}
7、編寫調用操作Excel類的方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.zzg.deployData;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
public class DeployData {
private File fileOut;
public void excute(String filepath) {
fileOut = new File(filepath);
this.deployUserInfoData();
}
public void deployUserInfoData() {
try {
new OperExcel(fileOut, "test", Object.class, "Sheet1");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
8、編寫客戶端
1
2
3
4
5
6
7
8
9
10
package com.zzg.client;
import com.zzg.deployData.DeployData;
public class DeployClient {
public static void main(String[] args) {
DeployData deployData = new DeployData();
deployData.excute("D://test.xls");
}
}
Ⅲ 如何用java把數據寫入到excel
添加Spire.Xls.jar依賴，可以創建Excel,或者對現有Excel文檔進行處理。
1.寫入數據到指定單元格
//CreateaWorkbookinstance
Workbookwb=newWorkbook();
//Getthefirstworksheet
Worksheetsheet=wb.getWorksheets().get(0);
//Writetextinthespecificcell
sheet.getCellRange(1,1).setText("HelloWorld");
//Savethefile
wb.saveToFile("HelloWorld.xlsx",ExcelVersion.Version2016)
2. 將數組導入Excel
//CreateaWorkbookinstance
Workbookwb=newWorkbook();
//Getthefirstworksheet
Worksheetsheet=wb.getWorksheets().get(0);
//Insertanarraytothefirstcolumn()
String[]stringArray=newString[]{"Apple","Pear","Grape","Banana","Peach"}
sheet.insertArray(stringArray,1,1,true);
//Savethefile
wb.saveToFile("InsertArray.xlsx",ExcelVersion.Version2016);
Ⅳ java操作excel的問題
可以是一下GCExcel這個組件，相較POI，GCExcel在功能上更加完善，並且完全參照Excel的規范。如果想要刪除行不留空白行，直接在GCExcel調用刪除命令就可以了，不需要再做上移的操作，更加易於理解。刪除行時只需要輸入行的名稱或者索引，像這樣：
worksheet2.getRange("A3:A5").getEntireRow().delete();
或worksheet2.getRange("2:4").delete(); 支持刪除多行。
參考文檔：網頁鏈接
Ⅳ java 如何寫excel
public static void main(String[] args) throws IOException {
//創建工作簿對象
HSSFWorkbook wb=new HSSFWorkbook();
//創建工作表對象
HSSFSheet sheet=wb.createSheet("我的工作表");
//創建繪圖對象
HSSFPatriarch p=sheet.createDrawingPatriarch();
//創建單元格對象,批註插入到4行,1列,B5單元格
HSSFCell cell=sheet.createRow(4).createCell(1);
//插入單元格內容
cell.setCellValue(new HSSFRichTextString("批註"));
//獲取批註對象
//(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
//前四個參數是坐標點,後四個參數是編輯和顯示批註時的大小.
HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
//輸入批註信息
comment.setString(new HSSFRichTextString("插件批註成功!插件批註成功!"));
//添加作者,選中B5單元格,看狀態欄
comment.setAuthor("toad");
//將批註添加到單元格對象中
cell.setCellComment(comment);
//創建輸出流
FileOutputStream out=new FileOutputStream("writerPostil.xls");
wb.write(out);
//關閉流對象
out.close();
}
Aspose.cell for Java+Aspose.PDF for Java 可以實現，但是網上Aspose的jar包貌似沒破解的，.net的倒挺多.
Ⅵ java修改Excel
給你個我以前寫的小例子好了
怎麼也得改改才好用，是用的jxl的庫，你可以從網上下載一個jxl.jar導到你的項目里
看看它的API有不少例子，好像是韓國人寫的寫點小東西還好有BUG，大項目的話用apache的POI
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
public class exportEXL {
String fileName= "F:\\NumberProcessed.xls";
String sheetName ="sheet";
private int location = 1;
public void ExportFile(int[] arr) throws WriteException, IOException{
WritableWorkbook wwb = null;
//字體設置，全部參數集中於format變數中
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false);
DisplayFormat displayFormat = NumberFormats.INTEGER;
WritableCellFormat format = new WritableCellFormat(wf,displayFormat);
format.setAlignment(jxl.format.Alignment.RIGHT);
format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.NONE);
try {
// 創建可寫入的工作簿對象
wwb = Workbook.createWorkbook(new File(fileName));
if (wwb != null) {
// 在工作簿里創建可寫入的工作表，第一個參數為工作表名，第二個參數為該工作表的所在位置
WritableSheet ws = wwb.createSheet(sheetName, location);
if (ws != null) {
/* 添加表結構 */
for(int j =1;j<=93;j++){
for(int i=0;i<arr.length;i++)
{
//number參數中第一個參數是列，從零開始
//第二個參數是行，從零開始，此程序中每隔75行重復一次
jxl.write.Number number = new jxl.write.Number(0,
i+(j-1)*arr.length,
Integer.parseInt(Integer.toString(arr[i])),format);
//寫入單元格
ws.addCell(number);
arr[i] = arr[i]+500;
}
}
}
}
// 從內存中寫入到文件
wwb.write();
System.out.println("路徑為：" + fileName + "的工作簿寫入數據成功！");
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
wwb.close();
}
}
}
Ⅶ java讀取和輸出excel表格的問題
Java Excel 是一個開源項目，通過它Java開發人員可以讀取Excel文件的內容、創建新的Excel文件、更新已經存在的Excel文件等，在項目中需要導入名為jxl.jar的包。在這里只是示例它的基本用法，其他高級的功能（圖片、公式、格式等）請參考Java Excel的幫助文檔。
如有一個用戶資料的Excel表，包含ID、用戶名、性別、郵件等信息，定義一個用戶JavaBean：
packagecom.monitor1394.excel;
/**
*
*用戶
*
*@authormonitor
*Createdon2010-12-22,9:57:58
*/
publicclassUser{
/**ID*/
privateintid;
/**用戶名*/
privateStringname;
/**性別1:男2:女*/
privateintsex;
/**郵件*/
privateStringemail;
publicUser(){
}
publicUser(intid,Stringname,intsex,Stringemail){
this.id=id;
this.name=name;
this.sex=sex;
this.email=email;
}
publicStringgetEmail(){
returnemail;
}
publicvoidsetEmail(Stringemail){
this.email=email;
}
publicintgetId(){
returnid;
}
publicvoidsetId(intid){
this.id=id;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
publicintgetSex(){
returnsex;
}
publicvoidsetSex(intsex){
this.sex=sex;
}
@Override
publicStringtoString(){
returnid+":"+name;
}
}
提供的Excel表操作類如下，某些單元格的格式可按自己意願指定：
packagecom.monitor1394.excel;
importjava.io.File;
importjava.io.IOException;
importjava.util.ArrayList;
importjava.util.List;
importjxl.Sheet;
importjxl.Workbook;
importjxl.format.Alignment;
importjxl.format.Border;
importjxl.format.BorderLineStyle;
importjxl.format.Colour;
importjxl.format.VerticalAlignment;
importjxl.read.biff.BiffException;
importjxl.write.Label;
importjxl.write.Number;
importjxl.write.NumberFormats;
importjxl.write.WritableCellFormat;
importjxl.write.WritableFont;
importjxl.write.WritableSheet;
importjxl.write.WritableWorkbook;
importjxl.write.WriteException;
/**
*
*Excel表操作
*
*@authormonitor
*Createdon2010-12-22,9:50:28
*/
publicclassExcel{
/**標題單元格格式*/
=null;
/**主題內容單元格格式*/
=null;
/**注釋單元格格式*/
=null;
/**浮點型數據的單元格格式*/
=null;
/**整型數據的單元格格式*/
=null;
/**初始化數據*/
privatestaticbooleaninit=false;
/**私有構造方法，防止錯誤使用Excel類*/
privateExcel(){
}
/**
*初始化各單元格格式
*@throwsWriteException初始化失敗
*/
privatestaticvoidinit()throwsWriteException{
WritableFontfont1,font2,font3,font4;
//Arial字體，9號，粗體，單元格黃色，田字邊框，居中對齊
font1=newWritableFont(WritableFont.ARIAL,9,WritableFont.BOLD,false);
titleFormat=newWritableCellFormat(font1);
titleFormat.setBackground(Colour.YELLOW);
titleFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
titleFormat.setAlignment(Alignment.CENTRE);
//Arial字體，9號，粗體，單元格黃色，田字邊框，左右居中對齊，垂直居中對齊，自動換行
font2=newWritableFont(WritableFont.ARIAL,9,WritableFont.BOLD,false);
noteFormat=newWritableCellFormat(font2);
noteFormat.setBackground(Colour.YELLOW);
noteFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
noteFormat.setAlignment(Alignment.CENTRE);
noteFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
noteFormat.setWrap(true);
//Arial字體，9號，非粗體，單元格淡綠色，田字邊框
font3=newWritableFont(WritableFont.ARIAL,9,WritableFont.NO_BOLD,false);
bodyFormat=newWritableCellFormat(font3);
bodyFormat.setBackground(Colour.LIGHT_GREEN);
bodyFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
//Arial字體，9號，非粗體，單元格淡綠色，田字邊框
font4=newWritableFont(WritableFont.ARIAL,9,WritableFont.NO_BOLD,false);
floatFormat=newWritableCellFormat(font4,NumberFormats.FLOAT);
floatFormat.setBackground(Colour.LIGHT_GREEN);
floatFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
//Arial字體，9號，非粗體，單元格淡綠色，田字邊框
font4=newWritableFont(WritableFont.ARIAL,9,WritableFont.NO_BOLD,false);
intFormat=newWritableCellFormat(font4,NumberFormats.INTEGER);
intFormat.setBackground(Colour.LIGHT_GREEN);
intFormat.setBorder(Border.ALL,BorderLineStyle.THIN);
init=true;
}
(List<User>userList,FiledestFile)throwsWriteException,IOException{
if(init==false)init();
intindex,row;
WritableSheetsheet=null;
WritableWorkbookbook=null;
book=Workbook.createWorkbook(destFile);
sheet=book.createSheet("用戶表",0);
sheet.setColumnView(0,15);
sheet.setColumnView(1,15);
sheet.setColumnView(2,15);
sheet.setColumnView(3,40);
//欄位變數名
index=0;
sheet.addCell(newLabel(index++,0,"id",titleFormat));
sheet.addCell(newLabel(index++,0,"name",titleFormat));
sheet.addCell(newLabel(index++,0,"sex",titleFormat));
sheet.addCell(newLabel(index++,0,"email",titleFormat));
//欄位名
index=0;
sheet.addCell(newLabel(index++,1,"ID",titleFormat));
sheet.addCell(newLabel(index++,1,"用戶名",titleFormat));
sheet.addCell(newLabel(index++,1,"性別",titleFormat));
sheet.addCell(newLabel(index++,1,"郵件",titleFormat));
//欄位注釋
index=0;
sheet.addCell(newLabel(index++,2,null,noteFormat));
sheet.addCell(newLabel(index++,2,null,noteFormat));
sheet.addCell(newLabel(index++,2,"1:男/n2:女",noteFormat));
sheet.addCell(newLabel(index++,2,null,noteFormat));
row=3;
for(Useruser:userList){
if(user==null)continue;
index=0;
sheet.addCell(newNumber(index++,row,user.getId(),bodyFormat));
sheet.addCell(newLabel(index++,row,user.getName(),bodyFormat));
sheet.addCell(newNumber(index++,row,user.getSex(),bodyFormat));
sheet.addCell(newLabel(index++,row,user.getEmail(),bodyFormat));
row++;
}
book.write();
if(book!=null)book.close();
}
publicstaticList<User>readUserExcelFile(Filefile)throwsIOException,BiffException{
if(file==null)returnnull;
introw,column;
Stringtemp=null;
Workbookbook=null;
Sheetsheet=null;
List<User>userList=newArrayList<User>();
book=Workbook.getWorkbook(file);
sheet=book.getSheet(0);
row=3;
while(row<sheet.getRows()){
column=0;
Useruser=newUser();
//id
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null&&!temp.equals("")&&temp.matches("//d+"))user.setId(Integer.parseInt(temp));
elsebreak;
//名稱
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null&&!temp.equals(""))user.setName(temp);
//性別
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null&&!temp.equals("")&&temp.matches("//d+"))user.setSex(Integer.parseInt(temp));
//郵件
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null&&!temp.equals(""))user.setEmail(temp);
userList.add(user);
row++;
}
if(book!=null)book.close();
returnuserList;
}
}
要導入的Excel表格式如下：
Ⅷ Java對Excel解析(求助)
這篇blog主要是講述java中poi讀取excel，而excel的版本包括：2003-2007和2010兩個版本， 即excel的後綴名為：xls和xlsx。
讀取excel和MySQL相關： java的poi技術讀取Excel數據到MySQL
代碼如下
/**
*
*/
packagecom.b510.common;
/**
*@authorHongten
*@created2014-5-21
*/
publicclassCommon{
publicstaticfinalStringOFFICE_EXCEL_2003_POSTFIX="xls";
publicstaticfinalStringOFFICE_EXCEL_2010_POSTFIX="xlsx";
publicstaticfinalStringEMPTY="";
publicstaticfinalStringPOINT=".";
publicstaticfinalStringLIB_PATH="lib";
_INFO_XLS_PATH=LIB_PATH+"/student_info"+POINT+OFFICE_EXCEL_2003_POSTFIX;
_INFO_XLSX_PATH=LIB_PATH+"/student_info"+POINT+OFFICE_EXCEL_2010_POSTFIX;
publicstaticfinalStringNOT_EXCEL_FILE=":NottheExcelfile!";
="Processing...";
}
/**
*
*/
packagecom.b510.excel;
importjava.io.FileInputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.util.ArrayList;
importjava.util.List;
importorg.apache.poi.hssf.usermodel.HSSFCell;
importorg.apache.poi.hssf.usermodel.HSSFRow;
importorg.apache.poi.hssf.usermodel.HSSFSheet;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.xssf.usermodel.XSSFCell;
importorg.apache.poi.xssf.usermodel.XSSFRow;
importorg.apache.poi.xssf.usermodel.XSSFSheet;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
importcom.b510.common.Common;
importcom.b510.excel.util.Util;
importcom.b510.excel.vo.Student;
/**
*@authorHongten
*@created2014-5-20
*/
publicclassReadExcel{
/**
*readtheExcelfile
*@
*@return
*@throwsIOException
*/
publicList<Student>readExcel(Stringpath)throwsIOException{
if(path==null||Common.EMPTY.equals(path)){
returnnull;
}else{
Stringpostfix=Util.getPostfix(path);
if(!Common.EMPTY.equals(postfix)){
if(Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
returnreadXls(path);
}elseif(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
returnreadXlsx(path);
}
}else{
System.out.println(path+Common.NOT_EXCEL_FILE);
}
}
returnnull;
}
/**
*ReadtheExcel2010
*@
*@return
*@throwsIOException
*/
publicList<Student>readXlsx(Stringpath)throwsIOException{
System.out.println(Common.PROCESSING+path);
InputStreamis=newFileInputStream(path);
XSSFWorkbookxssfWorkbook=newXSSFWorkbook(is);
Studentstudent=null;
List<Student>list=newArrayList<Student>();
//ReadtheSheet
for(intnumSheet=0;numSheet<xssfWorkbook.getNumberOfSheets();numSheet++){
XSSFSheetxssfSheet=xssfWorkbook.getSheetAt(numSheet);
if(xssfSheet==null){
continue;
}
//ReadtheRow
for(introwNum=1;rowNum<=xssfSheet.getLastRowNum();rowNum++){
XSSFRowxssfRow=xssfSheet.getRow(rowNum);
if(xssfRow!=null){
student=newStudent();
XSSFCellno=xssfRow.getCell(0);
XSSFCellname=xssfRow.getCell(1);
XSSFCellage=xssfRow.getCell(2);
XSSFCellscore=xssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
returnlist;
}
/**
*ReadtheExcel2003-2007
*@parampaththepathoftheExcel
*@return
*@throwsIOException
*/
publicList<Student>readXls(Stringpath)throwsIOException{
System.out.println(Common.PROCESSING+path);
InputStreamis=newFileInputStream(path);
HSSFWorkbookhssfWorkbook=newHSSFWorkbook(is);
Studentstudent=null;
List<Student>list=newArrayList<Student>();
//ReadtheSheet
for(intnumSheet=0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){
HSSFSheethssfSheet=hssfWorkbook.getSheetAt(numSheet);
if(hssfSheet==null){
continue;
}
//ReadtheRow
for(introwNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRowhssfRow=hssfSheet.getRow(rowNum);
if(hssfRow!=null){
student=newStudent();
HSSFCellno=hssfRow.getCell(0);
HSSFCellname=hssfRow.getCell(1);
HSSFCellage=hssfRow.getCell(2);
HSSFCellscore=hssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
returnlist;
}
@SuppressWarnings("static-access")
privateStringgetValue(XSSFCellxssfRow){
if(xssfRow.getCellType()==xssfRow.CELL_TYPE_BOOLEAN){
returnString.valueOf(xssfRow.getBooleanCellValue());
}elseif(xssfRow.getCellType()==xssfRow.CELL_TYPE_NUMERIC){
returnString.valueOf(xssfRow.getNumericCellValue());
}else{
returnString.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
privateStringgetValue(HSSFCellhssfCell){
if(hssfCell.getCellType()==hssfCell.CELL_TYPE_BOOLEAN){
returnString.valueOf(hssfCell.getBooleanCellValue());
}elseif(hssfCell.getCellType()==hssfCell.CELL_TYPE_NUMERIC){
returnString.valueOf(hssfCell.getNumericCellValue());
}else{
returnString.valueOf(hssfCell.getStringCellValue());
}
}
}
/**
*
*/
packagecom.b510.excel.client;
importjava.io.IOException;
importjava.util.List;
importcom.b510.common.Common;
importcom.b510.excel.ReadExcel;
importcom.b510.excel.vo.Student;
/**
*@authorHongten
*@created2014-5-21
*/
publicclassClient{
publicstaticvoidmain(String[]args)throwsIOException{
Stringexcel2003_2007=Common.STUDENT_INFO_XLS_PATH;
Stringexcel2010=Common.STUDENT_INFO_XLSX_PATH;
//readthe2003-2007excel
List<Student>list=newReadExcel().readExcel(excel2003_2007);
if(list!=null){
for(Studentstudent:list){
System.out.println("No.:"+student.getNo()+",name:"+student.getName()+",age:"+student.getAge()+",score:"+student.getScore());
}
}
System.out.println("======================================");
//readthe2010excel
List<Student>list1=newReadExcel().readExcel(excel2010);
if(list1!=null){
for(Studentstudent:list1){
System.out.println("No.:"+student.getNo()+",name:"+student.getName()+",age:"+student.getAge()+",score:"+student.getScore());
}
}
}
}
/**
*
*/
packagecom.b510.excel.util;
importcom.b510.common.Common;
/**
*@authorHongten
*@created2014-5-21
*/
publicclassUtil{
/**
*getpostfixofthepath
*@parampath
*@return
*/
publicstaticStringgetPostfix(Stringpath){
if(path==null||Common.EMPTY.equals(path.trim())){
returnCommon.EMPTY;
}
if(path.contains(Common.POINT)){
returnpath.substring(path.lastIndexOf(Common.POINT)+1,path.length());
}
returnCommon.EMPTY;
}
}
/**
*
*/
packagecom.b510.excel.vo;
/**
*Student
*
*@authorHongten
*@created2014-5-18
*/
publicclassStudent{
/**
*id
*/
privateIntegerid;
/**
*學號
*/
privateStringno;
/**
*姓名
*/
privateStringname;
/**
*學院
*/
privateStringage;
/**
*成績
*/
privatefloatscore;
publicIntegergetId(){
returnid;
}
publicvoidsetId(Integerid){
this.id=id;
}
publicStringgetNo(){
returnno;
}
publicvoidsetNo(Stringno){
this.no=no;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
publicStringgetAge(){
returnage;
}
publicvoidsetAge(Stringage){
this.age=age;
}
publicfloatgetScore(){
returnscore;
}
publicvoidsetScore(floatscore){
this.score=score;
}
}
Ⅸ java 怎麼樣把多個Excel 合並為一個
程序中引用spire.xls.jar包
import com.spire.xls.*;
public class MergeExcels {
public static void main(String[] args){
//將待合並Excel文檔的名稱保存至字元串數組
String[] inputFiles = new String[]{"file1.xlsx","file2.xlsx"};
//創建一個新的Excel文檔
Workbook newBook = new Workbook();
//清除默認的3張工作表
newBook.getWorksheets().clear();
//創建另一個Excel文檔
Workbook tempBook = new Workbook();
//遍歷數組，依次載入每個Excel文檔並將文檔中的所有工作表復制到新建的Excel文檔中
for (String file : inputFiles)
{
tempBook.loadFromFile(file);
for (Worksheet sheet : (Iterable)tempBook.getWorksheets())
{
newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
}
}
//保存
newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
}
}
Ⅹ 如何用java來列印excel
importjava.io.File;
importjxl.Workbook;
importjxl.write.Label;
importjxl.write.WritableSheet;
importjxl.write.WritableWorkbook;
/**
*Excel導出（你需要引入jxl的jar包）
*/
publicclassTest{
publicstaticvoidmain(String[]args)
{
Testtest=newTest();
test.exportExcel();
}
/**
*導出(導出到磁碟)
*/
publicvoidexportExcel(){
WritableWorkbookbook=null;
try{
//打開文件
book=Workbook.createWorkbook(newFile("D:/測試.xls"));
//生成名為"學生"的工作表，參數0表示這是第一頁
WritableSheetsheet=book.createSheet("學生",0);
//指定單元格位置是第一列第一行(0,0)以及單元格內容為張三
Labellabel=newLabel(0,0,"張三");
//將定義好的單元格添加到工作表中
sheet.addCell(label);
//保存數字的單元格必須使用Number的完整包路徑
jxl.write.Numbernumber=newjxl.write.Number(1,0,30);
sheet.addCell(number);
//寫入數據並關閉文件
book.write();
}catch(Exceptione){
System.out.println(e);
}finally{
if(book!=null){
try{
book.close();
}catch(Exceptione){
e.printStackTrace();
}
}
}
}
}