java中使用excle_Java 操作 Excel

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

{

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

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

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

}

}