Excel文件是我们生活中常见的表格形式,怎么用Java来实现,看看下面的例子就可以知道怎么操作
[color=red][b]1.读取指定路径的Excel[/b][/color]
[color=blue]ReadExcel.java[/color]
[color=red][b]1.生成指定路径的Excel[/b][/color]
[color=blue]WriteExcel.java[/color]
[color=red][b]1.读取指定路径的Excel[/b][/color]
[color=blue]ReadExcel.java[/color]
package com.cyberwise.excel;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* title:读取一个Excel文件
* @author Lyong
*
*/
public class ReadExcel {
public static void readExcelFile(){
Workbook wb;
try {
wb = Workbook.getWorkbook(new File("F:/prjInfo.xls"));
Sheet s = wb.getSheet(0);//第一个sheeet
Cell c;
int row = s.getRows();//总行数
int col = s.getColumns();//总列数
for(int i = 0;i<row;i++){
for(int j = 0;j<col;j++){
c = s.getCell(j, i);
System.out.println(c.getContents()+" ");
}
//换行
System.out.println();
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
ReadExcel.readExcelFile();
}
}
[color=red][b]1.生成指定路径的Excel[/b][/color]
[color=blue]WriteExcel.java[/color]
package com.cyberwise.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class WriteExcel {
public static void createExcel(){
//输出Excel文件名
String tragetFile = "F:/test.xls";
//输出Excel文件工作表名
String worksheet = "Test";
//Excel工作表的标题
String[] title = {"姓名","年龄","地址"};
WritableWorkbook workbook = null;
try {
OutputStream os = new FileOutputStream(tragetFile);
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(worksheet, 0);//添加第一个工作表
//合并单元格
sheet.mergeCells(0, 0, 2, 1);
Label label = null;
label = new Label(0,0,"员工基本信息");
sheet.addCell(label);
insertSheet(label,sheet);
for(int i=0;i<title.length;i++){
//Label(列号,行号,内容)
label = new Label(i,2,title[i]);//把标题放到第一行
sheet.addCell(label);
}
setSheet(sheet);
workbook.write();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}finally{
if(workbook != null){
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/*
* 设置sheet的样式
*/
public static void setSheet(WritableSheet sheet){
sheet.getSettings().setProtected(true);//设置xls保护,单元格不能被修改
sheet.getSettings().setDefaultColumnWidth(20);//设置列的默认宽度
try {
//定义字体
WritableFont font = new WritableFont(WritableFont.TIMES,24,WritableFont.BOLD);
font.setColour(Colour.RED);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
/*
* 写入内容
*/
public static void insertSheet(Label label,WritableSheet sheet){
try {
label = new Label(0,3,"李四");
sheet.addCell(label);
label = new Label(1,3,"22");
sheet.addCell(label);
label = new Label(2,4,"湖南长沙");
sheet.addCell(label);
label = new Label(0,4,"张三");
sheet.addCell(label);
label = new Label(1,4,"22");
sheet.addCell(label);
label = new Label(2,4,"湖南株洲");
sheet.addCell(label);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
WriteExcel.createExcel();
}
}