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();
}
}
}
}
}