jxl现在已经没有升级版本只操作2003版本
public class JExcelAPIdo {
public void createone(){try {
// 打开文件
WritableWorkbook book = Workbook.createWorkbook(new File("D:/测试.xls"));
// 生成名为“第一页”的工作表,参数0表示这是第一页
WritableSheet sheet = book.createSheet("第一页", 0);
// 在Label对象的构造中指名单元格位置是第一列第一行(0,0)(x,y)
Label label = new Label(0, 0, "ID");
Label label2 = new Label(1, 0, "姓名");
Label label3 = new Label(2, 0, "性别");
Label label4 = new Label(3, 0, "年龄");
Label label5 = new Label(4, 0, "创建日期");
// 将定义好的单元格添加到工作表中
sheet.addCell(label);
sheet.addCell(label2);
sheet.addCell(label3);
sheet.addCell(label4);
sheet.addCell(label5);
/*
* * 生成一个保存数字的单元格 必须使用Number的完整包路径,否则有语法歧义 单元格位置是第二列,第一行,值为789.123
* */
jxl.write.Number number = new jxl.write.Number(0, 1, 001);
Label vlabel=new Label(1, 1, "蔡依林");
Label vlabel2=new Label(2, 1, "女");
jxl.write.Number vnumber = new jxl.write.Number(3, 1, 30);
DateTime dateTime=new DateTime(4, 1, new Date());
sheet.addCell(number);
sheet.addCell(vlabel);
sheet.addCell(vlabel2);
sheet.addCell(vnumber);
sheet.addCell(dateTime);
// 写入数据并关闭文件
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
@Test
public void getexcel(){
int ID=0;
String name="";
String sex="";
int age=0;
Date date=null;
try {
Workbook book = Workbook.getWorkbook(new File("D:/测试.xls"));
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第2列第2行的单元格
Cell cell1 = sheet.getCell(0, 1);
Cell cell2 = sheet.getCell(1, 1);
Cell cell3 = sheet.getCell(2, 1);
Cell cell4 = sheet.getCell(3, 1);
Cell cell5 = sheet.getCell(4, 1);
//String result = cell1.getContents();
//System.out.println(result);
//判断数据类型
if(cell1.getType()==CellType.NUMBER){
NumberCell numbercell=(NumberCell)cell1;
ID=(int) numbercell.getValue();
}
if(cell2.getType()==CellType.LABEL){
LabelCell labelcell=(LabelCell)cell2;
name=labelcell.getString();
}if(cell3.getType()==CellType.LABEL){
LabelCell labelcell3=(LabelCell)cell3;
sex=labelcell3.getString();
}
if(cell4.getType()==CellType.NUMBER){
NumberCell numbercell4=(NumberCell)cell4;
age=(int) numbercell4.getValue();
}
if(cell5.getType()==CellType.DATE){
DateCell dateCell=(DateCell)cell5;
date=dateCell.getDate();
}
System.out.println(cell1.getType()+","+ID);
System.out.println(cell2.getType()+","+name);
System.out.println(cell3.getType()+","+sex);
System.out.println(cell4.getType()+","+age);
System.out.println(cell5.getType()+","+date.toLocaleString());
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
//修改excel
@Test
public void modefy() throws Exception{
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File("D:/测试.xls"));
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(new File("D:/测试.xls"),wb);
// 添加一个工作表
WritableSheet sheet = book.createSheet("第二页", 1);
sheet.addCell(new Label(0, 0, "第二页的测试数据"));
book.write();
book.close();
}
//进阶阶段 数据格式
/*字符串的格式化涉及到的是字体、粗细、字号等元素,
这些功能主要由WritableFont和WritableCellFormat类来负责
* */
@Test
public void shujuformat() throws Exception{
//设置字体格式为excel支持的格式
WritableFont font1 = new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD); //①
font1.setColour(Colour.RED);
WritableCellFormat format1=new WritableCellFormat(font1); // ②
Label label=new Label(0,1,"字体格式",format1);// ③
/*其中①指定了字串格式:字体为TIMES,字号16,加粗显示。WritableFont有非常丰富的构造子,供不同情况下使用,jExcelAPI的java-doc中有详细列表,这里不再列出。
* ②处代码使用了WritableCellFormat类,这个类非常重要,通过它可以指定单元格的各种属性,后面的单元格格式化中会有更多描述。
③处使用了Label类的构造子,指定了字串被赋予那种格式。*/
//把水平对齐方式指定为居中
format1.setAlignment(jxl.format.Alignment.CENTRE);
//把垂直对齐方式指定为居中
format1.setVerticalAlignment(jxl.format.VerticalAlignment.BOTTOM);
//设置自动换行
format1.setWrap(true);
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File("D:/测试.xls"));
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(new File("D:/测试.xls"),wb);
// 获取工作表
WritableSheet sheet = book.getSheet("第二页");
sheet.setColumnView(0, 20);//设置第一列列宽为50
sheet.addCell(label);
book.write();
book.close();
}
//数字格式、日期格式
@Test
public void numberformat() throws Exception{
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File("D:/测试.xls"));
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(new File("D:/测试.xls"),wb);
// 获取工作表
WritableSheet sheet = book.getSheet("第二页");
//单元格值分别为3和3.14
//设置单元格类型
WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);
jxl.write.Number number2 = new jxl.write.Number(0, 2, 3.141519, integerFormat);
sheet.addCell(number2);
WritableCellFormat floatFormat = new WritableCellFormat (NumberFormats.FLOAT);
jxl.write.Number number3 = new jxl.write.Number(1, 2, 3.141519, floatFormat);
sheet.addCell(number3);
NumberFormat format = new NumberFormat("#.#####"); //五位小数四舍五入
WritableFont font1 = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
//WritableCellFormat fivedpsFormat = new WritableCellFormat(format);
WritableCellFormat fivedpsFormat2 = new WritableCellFormat(font1, format);
jxl.write.Number number4 = new jxl.write.Number(0, 3, 3.141519, fivedpsFormat2);
sheet.addCell(number4);
//日期格式
Date nowtime = Calendar.getInstance().getTime();
DateFormat customDateFormat = new DateFormat ("yyyy/mm/dd hh:MM:ss");
WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
DateTime dateCell = new DateTime(0, 4, nowtime, dateFormat);
sheet.addCell(dateCell);
book.write();
book.close();
}
//单元格操作
@Test
public void makecell()throws Exception{
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File("D:/测试.xls"));
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(new File("D:/测试.xls"),wb);
// 获取工作表
WritableSheet sheet = book.getSheet("第二页");
//只有放在第一个位置的将会在合并后的单元格中存在,其他单元格的数据将被丢弃
//合并第二列第6行(1,5)到第5列第9行(4,8)的所有单元格
//sheet.mergeCells(1,5,4,8);
Label label=new Label(1, 5, "合并单元格");
sheet.addCell(label);
/*合并既可以是横向的,也可以是纵向的。
*合并后的单元格不能再次进行合并,否则会触发异常。
*当试图合并一个已经合并的单元格的时候,
*如果数量少的话,只会在面板上打出几个warning,数量大的话,整个文件将无法输出*/
//设置行高 指定第i+1行的高度
sheet.setRowView(10,600); //exel默认行高是300
sheet.setColumnView(4, 30);
book.write();
book.close();
}
//图片操作 excel 对图片有格式要求 jpg格式不支持
@Test
public void imageXLS()throws Exception{
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File("D:/测试.xls"));
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(new File("D:/测试.xls"),wb);
// 获取工作表
WritableSheet sheet = book.getSheet("第一页");
File file = new File("D:/plain.png");
WritableImage image = new WritableImage(3, 4, 6, 10, file);
sheet.addImage(image);
book.write();
book.close();
}
@Test
public void copyexcel(){
try {
Workbook workbook = Workbook.getWorkbook(new File("D:/开发资料/POIandJXL/test2003.xls"));
WritableWorkbook copy = Workbook.createWorkbook(new File("D:/开发资料/POIandJXL/copy.xls"), workbook);
} catch (Exception e) {
e.printStackTrace();
}
}
//读操作进阶
@Test
public void getexcel(){
try {
// Excel文件URL
String path = "D:/开发资料/POIandJXL/test2003.xls";
// 写入到FileInputStream
InputStream is = new FileInputStream(path);
// 得到工作薄
jxl.Workbook wb = Workbook.getWorkbook(is);
// 得到工作薄中的第一个工作表
jxl.Sheet sheet = wb.getSheet(0);
// 得到工作表的第一个单元格,即A1
Cell cell = sheet.getCell(0, 0);
// getContents()将Cell中的数据转为字符串
String content = cell.getContents();
System.out.println("获取数据:"+content);
System.out.println("行书:"+sheet.getRows()+",列数:"+sheet.getColumns());
// 关闭工作薄 关闭输入流
wb.close();
is.close();
/* 可以通过Sheet的getCell(x,y)方法得到任意一个单元格还可以通过Sheet的getRows(),
* getColumns()方法得到行数列数,并用于循环控制,输出一个sheet中的所有内容*/
} catch (Exception e) {
e.printStackTrace();
}
}
//写操作进阶
@Test
public void writerexcel(){
try {
OutputStream os = new FileOutputStream("D:/开发资料/POIandJXL/test003test.xls");
// 输出的Excel文件URL
WritableWorkbook workbook = Workbook.createWorkbook(os);
// 创建可写工作 薄
WritableSheet sheet = workbook.createSheet("sheet1", 0);
// 创建可写工作表
Label labelCF = new Label(0, 0, "hello");
// 创建写入位置和内容
sheet.addCell(labelCF);
// 将Label写入sheet中
WritableFont font = new WritableFont(WritableFont.TIMES, 12,WritableFont.BOLD, false);
// 设置写入字体
WritableCellFormat wcfF = new WritableCellFormat(font);
// 设置CellFormat
Label labelCF1 = new Label(0, 2, "hello", wcfF);
// 创建写入位置,内容和格式
sheet.addCell(labelCF1);
// 将Label写入sheet中 // 现在可以写了
workbook.write(); // 写完后关闭
workbook.close(); // 输出流也关闭吧
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//导出报表
public void doexcel(){
/*ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
LinkedHashMap lhmap = new LinkedHashMap();
try {
conn = getConnection();
String sql = "select * from tablename";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(sql);
// 新建Excel文件
String filePath = request.getRealPath("aaa.xls");
File myFilePath = new File(filePath);
if (!myFilePath.exists()) myFilePath.createNewFile();
FileWriter resultFile = new FileWriter(myFilePath);
PrintWriter myFile = new PrintWriter(resultFile);
resultFile.close();
// 用JXL向新建的文件中添加内容
OutputStream outf = new FileOutputStream(filePath);
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(outf);
jxl.write.WritableSheet ws = wwb.createSheet("sheettest", 0);
int i = 0;
int j = 0;
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k, 0, rs.getMetaData().getColumnName(k + 1)));
}
while (rs.next()) {
System.out.println(rs.getMetaData().getColumnCount());
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k, j + i + 1, rs.getString(k + 1)));
}
i++;
}
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
response.sendRedirect("aaa.xls");*/
}
/*
2, 方法字典 Workbook类提供的方法
1. int getNumberOfSheets() 获得工作薄(Workbook)中工作表(Sheet)的个数,示例:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
int sheets = rwb.getNumberOfSheets();
2. Sheet[] getSheets() 返回工作薄(Workbook)中工作表(Sheet)对象数组,示例:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
Sheet[] sheets = rwb.getSheets();
3. String getVersion() 返回正在使用的API的版本号,好像是没什么太大的作用。
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
String apiVersion = rwb.getVersion();
Sheet接口提供的方法 :
1. String getName() 获取Sheet的名称,示例:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
String sheetName = rs.getName();
2. int getColumns() 获取Sheet表中所包含的总列数,示例:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsColumns = rs.getColumns();
3. Cell[] getColumn(int column)
-------这个和上面的不同,少了个s,不要看错了 获取某一列的所有单元格,返回的是单元格对象数组,示例:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getColumn(0);
4. int getRows() 获取Sheet表中所包含的总行数,示例:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsRows = rs.getRows();
5. Cell[] getRow(int row) 获取某一行的所有单元格,返回的是单元格对象数组,示例子:
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getRow(0);
6. Cell getCell(int column, int row) 获取指定单元格的对象引用,需要注意的是它的两个参数,第一个是列数,第二个是行
数,这与通常的行、列组合(第一个是行数,第二个是列数)有些不同。
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell cell = rs.getCell(0, 0);
*
* */
public void testwriter(){
try {
// 构建Workbook对象, 只读Workbook对象
// Method 1:创建可写入的Excel工作薄
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File("D:/开发资料/POIandJXL/test003writer.xls"));
// Method 2:将WritableWorkbook直接写入到输出流
/*
* * OutputStream os = new FileOutputStream(targetfile);
* * jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
* */
// 创建Excel工作表
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);
// 现在要做的只是实例化API所提供的Excel基本数据类型,并将它们添加到工作 表中就可以了
// 1.添加Label对象
jxl.write.Label labelC = new jxl.write.Label(0, 0,"This is a Label cell");
ws.addCell(labelC);
// 添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(1, 0,"This is a Label Cell", wcfF);
ws.addCell(labelCF);
// 添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(
WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC = new jxl.write.Label(1, 0,"This is a Label Cell", wcfFC);
ws.addCell(labelCF);
// 2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);
// 添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926,wcfN);
ws.addCell(labelNF);
// 3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
// 4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3,new java.util.Date());
ws.addCell(labelDT);
// 添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3,new java.util.Date(), wcfDF);
ws.addCell(labelDTF);
// 写入Exel工作表
wwb.write();
// 关闭Excel工作薄对象
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}