import java.io.File;
import java.io.IOException;
import java.util.List;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.Cell;
import jxl.Sheet;
import jxl.write.Number;
/**
* jxl只支持 xls 格式的Excel，不支持 xlsx 格式
*/
public class JxlExcelUtil {
/**
* 创建Excel
* @param excelFilePath
*/
public void CreateExcel(String excelFilePath) {
try {
// 打开文件
WritableWorkbook book = Workbook.createWorkbook(new File(excelFilePath));
// 判断sheet(0)是否已经存在，若存在，则删除
if (book.getSheet("第一页") != null) {
book.removeSheet(0);
}
// 生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet("第一页", 0);
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// 以及单元格内容为test
Label label = new Label(0, 0, "test");
// 将定义好的单元格添加到工作表中
sheet.addCell(label);
Number number = new Number(1, 0, 555.12541);
sheet.addCell(number);
// 写入数据并关闭文件
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
/**
* 指定读取单个Excel
* @param exeFilePath
*/
public void ReadExcel(String exeFilePath) {
try {
Workbook book = Workbook.getWorkbook(new File(exeFilePath));
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格
Cell cell1 = sheet.getCell(0, 0);
String result = cell1.getContents();
System.out.println(result);
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
/**
* 修改Excel内容
* @param exeFilePath
*/
public void UpdateExcel(String exeFilePath){
try {
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File(exeFilePath));
// 打开一个文件的副本，并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(new File(exeFilePath),
wb);
// 添加一个工作表
// WritableSheet sheet = book.createSheet("第3页", 2);
// 若”第二页“已经存在，在直接getSheet即可
WritableSheet sheet = book.getSheet("3.实际结果");
sheet.addCell(new Label(0, 0, "TestJet"));
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
/**
* 循环读取Excel内容
* @param excelFilePath
* */
public StringBuilder WhileReadExcel(String excelFilePath){
String apiurl = null;
StringBuilder apiUrl = new StringBuilder();
try {
Workbook book = Workbook.getWorkbook(new File(excelFilePath));
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一列第一行的单元格
int columnum = sheet.getColumns();// 得到列数
int rownum = sheet.getRows();// 得到行数
// System.out.println("总列数：" + columnum);
// System.out.println("总行数：" + rownum);
for(int i=1;i<rownum;i++)// 循环进行读写
{
Cell cellBaseUrl = sheet.getCell(0,i);
String baseUrl = cellBaseUrl.getContents();
Cell cellCity = sheet.getCell(1,i);
String city = cellCity.getContents();
Cell cellLanguage = sheet.getCell(2,i);
String language = cellLanguage.getContents();
apiurl = baseUrl + city + "&language=" + language + "&unit=c" + "\n";
// System.out.print(apiurl);
// 直接String apiurl, print(apiurl)是可以全部循环值；
// 但是后面的return时，如果break,则返回第一个值；若continue，则返回循环的最后一个值
// 必须使用数组的形式，后面的return才可以返回所有的值
apiUrl.append(apiurl);
// break;
// continue;
// 获取sheet中所有内容
// for (int j = 0; j < columnum; j++) {
// Cell cellN = sheet.getCell(j, i);
// String result = cellN.getContents();
// System.out.print(result);
// System.out.print("\t");
// }
// System.out.println();
}
book.close();
}
catch (Exception e) {
e.printStackTrace();
}return apiUrl;
}
/**
* 完全读取 1张excel表 数据到 另一张excel表中
* @param excelFilePath
* @param excelFilePathNew
*/
public void WriteExcel1ToExcel2(String excelFilePath,String excelFilePathNew){
try {
Workbook book = Workbook.getWorkbook(new File(excelFilePath));
Sheet sheet = book.getSheet(0);
WritableWorkbook book2 = Workbook.createWorkbook(new File(excelFilePathNew));
WritableSheet sheet2 = book2.createSheet("第一页", 0);
int columnum = sheet.getColumns();
int rownum = sheet.getRows();
for (int i = 0; i < rownum; i++)
{
for (int j = 0; j < columnum; j++)
{
Cell cellN = sheet.getCell(j, i);
String result ;
result = cellN.getContents();
System.out.print(result);
System.out.print("\t");
Label label = new Label(j, i, result);
sheet2.addCell(label);
}
System.out.println();
}
book2.write();
book2.close();
}catch (Exception e){
e.printStackTrace();
}
}
/**
* 完全读取 excel表sheet1 数据到 该excel表sheet2 中
* @param excelFilePath
*/
public void WriteSheet1ToSheet2(String excelFilePath){
try {
Workbook wb = Workbook.getWorkbook(new File(excelFilePath));
Sheet sheet1 = wb.getSheet(1);
WritableWorkbook book = Workbook.createWorkbook(new File(excelFilePath),
wb);
if (book.getSheet("3.实际结果") != null || book.getSheet("4.比对结果") != null) {
book.removeSheet(2);
book.removeSheet(3);
}
WritableSheet sheet2 = book.createSheet("3.实际结果", 2);
WritableSheet sheet3 = book.createSheet("4.比对结果", 3);
int columNum = sheet1.getColumns();
for (int j=0;j<columNum;j++){
Cell cell1 = sheet1.getCell(j,0);
String result1 = cell1.getContents();
Label label = new Label(j,0,result1);
sheet2.addCell(label);
}
for (int y=0;y<columNum;y++){
Cell cell1 = sheet1.getCell(y,0);
String result1 = cell1.getContents();
Label label = new Label(y,0,result1);
sheet3.addCell(label);
}
// 整表sheet数据复制
// int columNum = sheet0.getColumns();
// int rownum = sheet0.getRows();
// for (int i = 0; i < rownum; i++){
// for (int j = 0; j < columNum; j++) {
// Cell cellN = sheet0.getCell(j, i);
// String result = cellN.getContents();
// System.out.print(result);
// System.out.print("\t");
// Label label = new Label(j, i, result);
// sheet.addCell(label);
// }
// System.out.println();
// }
book.write();
book.close();
}catch (Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
String excelFilePath = "src/main/java/data/testXinZhi.xls";
// String excelFilePathNew = "src/main/java/data/test.xls";
JxlExcelUtil excel = new JxlExcelUtil();
// excel.CreateExcel();
// excel.ReadExcel();
// excel.UpdateExcel();
// excel.WhileReadExcel(excelFilePath);
// excel.WriteExcel1ToExcel2(excelFilePath,excelFilePathNew);
excel.WriteSheet1ToSheet2(excelFilePath);
}
}
网友评论