Java操作Excel文件，java操作excel

Java操作Excel文件，java操作excel

Java操作Excel文件（例子）

分类：Java

package com.winkee.wse.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.qeesoo.lib.DbConLib;
import com.winkee.wse.DBService;

public class JavaHandlerExcel {


public static void main(String[] args) {
// TODO Auto-generated method stub

}

private String dbconfig;

private String host;

private DbConLib conlib;

private DBService dbService;

private ResultSet webUpdateRs;

private List list;

public JavaHandlerExcel(String dbconfig, String host) throws Exception {
this.dbconfig = dbconfig;
this.host = host;
conlib = new DbConLib(this.dbconfig, this.host);
dbService = conlib.getDbService();
list = new ArrayList();
}

public void writeSqlExcel(String fileName, String sheetName, String sql)
throws Exception {
try {
webUpdateRs = dbService.executeQuery(sql);
} catch (Exception ex) {
ex.printStackTrace();
}
int columnCount = 0;
while (webUpdateRs.next()) {
columnCount = webUpdateRs.getMetaData().getColumnCount();
String str[] = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
str[i] = webUpdateRs.getString(i + 1);
}
list.add(str);
this.writeExcel(list, fileName, sheetName);
}
}

public void readExcel(String filePath, String tableName) throws Exception {
jxl.Workbook rwb = null;
StringBuilder insertSql = new StringBuilder("");
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
InputStream is = new FileInputStream(filePath);
rwb = Workbook.getWorkbook(is);
// Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
// Sheet的下标是从0开始
// 获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
// 获取Sheet表中所包含的总列数
int rsColumns = rs.getColumns();
// 获取Sheet表中所包含的总行数
int rsRows = rs.getRows();
// 获取指定单元格的对象引用
for (int i = 0; i < rsRows; i++) {
insertSql = new StringBuilder("insert into " + tableName
+ " values(");
for (int j = 0; j < rsColumns; j++) {
Cell cell = rs.getCell(j, i);
// System.out.print(cell.getContents() + " ");
insertSql.append("'" + cell.getContents() + "',");
}
insertSql.deleteCharAt(insertSql.length() - 1);
insertSql.append(")");
// System.out.println(insertSql.toString());
dbService.executeUpdate(insertSql.toString());
insertSql = new StringBuilder("");
}
} catch (Exception e) {
// e.printStackTrace();
} finally {
// 操作完成时，关闭对象，释放占用的内存空间
rwb.close();
dbService.close();
}
}

public void readExcel(String filePath) {
jxl.Workbook rwb = null;
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
InputStream is = new FileInputStream(filePath);
rwb = Workbook.getWorkbook(is);
// Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
// Sheet的下标是从0开始
// 获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
// 获取Sheet表中所包含的总列数
int rsColumns = rs.getColumns();
// 获取Sheet表中所包含的总行数
int rsRows = rs.getRows();
// 获取指定单元格的对象引用
for (int i = 0; i < rsRows; i++) {
for (int j = 0; j < rsColumns; j++) {
Cell cell = rs.getCell(j, i);
System.out.print(cell.getContents() + " ");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 操作完成时，关闭对象，释放占用的内存空间
rwb.close();

}
}

public void readExcel() {
jxl.Workbook rwb = null;
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
String readfile = "D://abc.xls";
InputStream is = new FileInputStream(readfile);
rwb = Workbook.getWorkbook(is);
// Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
// Sheet的下标是从0开始
// 获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
// 获取Sheet表中所包含的总列数
int rsColumns = rs.getColumns();
// 获取Sheet表中所包含的总行数
int rsRows = rs.getRows();
// 获取指定单元格的对象引用
for (int i = 0; i < rsRows; i++) {
for (int j = 0; j < rsColumns; j++) {
Cell cell = rs.getCell(j, i);
System.out.print(cell.getContents() + " ");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 操作完成时，关闭对象，释放占用的内存空间
rwb.close();

}
}

private void writeExcel(String fileName, String sheetName) {
WritableWorkbook wwb = null;
File writeFile = new File(fileName);
try {
// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(writeFile);
} catch (IOException e) {
e.printStackTrace();
}
if (wwb != null) {
// 创建一个可写入的工作表
// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet(sheetName, 0);

// 下面开始添加单元格
for (int i = 0; i < 10; i++) {
for (int j = 0; j < 5; j++) {
// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
Label labelC = new Label(j, i, "这是第" + (i + 1) + "行，第"
+ (j + 1) + "列");
try {
// 将生成的单元格添加到工作表中
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}

}
}

try {
// 从内存中写入文件中
wwb.write();
// 关闭资源，释放内存
wwb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}

}
}

private void writeExcel(List strs, String fileName,
String sheetName) {
WritableWorkbook wwb = null;
File writeFile = new File(fileName);
int columnCount = 0;
int rowCount = 0;
try {
// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(writeFile);
} catch (IOException e) {
e.printStackTrace();
}
if (wwb != null) {
// 创建一个可写入的工作表
// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet(sheetName, 0);

// 下面开始添加单元格
if (strs != null && strs.size() > 0) {
columnCount = strs.get(0).length;
rowCount = strs.size();
}
for (int i = 0; i < rowCount; i++) {
for (int j = 0; j < columnCount; j++) {
// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
String str = strs.get(i)[j];
Label labelC = new Label(j, i, str);

try {
// 将生成的单元格添加到工作表中
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}

}
}

try {
// 从内存中写入文件中
wwb.write();
// 关闭资源，释放内存
wwb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}

}
}

}


调用：

package com.winkee.wse.excel;

public class ReadTableData {


public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
JavaHandlerExcel jhe=new JavaHandlerExcel("./DbConConfig.xml", "127.0.0.1");
jhe.writeSqlExcel("./yes.xls","sheet","select id,name,province,city from t_cn_hc360_info limit 100");
jhe.readExcel("./yes.xls","test_companyinfo");
}
}

记得导入Jxl.jar包