jxl 管理excel

it2022-05-24 75

import java.io.File;

import junit.framework.TestCase;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.Colour;import jxl.format.VerticalAlignment;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;

public class TestExcel extends TestCase {

public void testExcel1() {

try { /** */ /** **********创建工作簿************ */ WritableWorkbook workbook = Workbook.createWorkbook(new File( "d:/留言信息.xls")); /** */ /** **********创建工作表************ */ WritableSheet sheet = workbook.createSheet("留言信息", 0);

/** */ /** *********设置列宽**************** */ sheet.setColumnView(0, 5); // 第1列 sheet.setColumnView(1, 12); // 第2列 sheet.setColumnView(2, 10); sheet.setColumnView(3, 22); sheet.setColumnView(4, 15); sheet.setColumnView(5, 23); sheet.setColumnView(6, 40); // 设置行高 sheet.setRowView(0, 1600, false); sheet.setRowView(1, 400, false); sheet.setRowView(7, 400, false); // 设置页边距 sheet.getSettings().setRightMargin(0.5); // 设置页脚 sheet.setFooter("", "", "测试页脚"); /** */ /** ************设置单元格字体************** */ // 字体 WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10); WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD); WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD); WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

/** */ /** ************以下设置几种格式的单元格************ */ // 用于标题 WritableCellFormat wcf_title = new WritableCellFormat(BoldFont); wcf_title.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条 wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐 wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐 wcf_title.setWrap(true); // 是否换行

// 用于表格标题 WritableCellFormat wcf_tabletitle = new WritableCellFormat( tableFont); wcf_tabletitle.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条 wcf_tabletitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐 wcf_tabletitle.setAlignment(Alignment.CENTRE); // 水平对齐 wcf_tabletitle.setWrap(true); // 是否换行

// 用于正文左 WritableCellFormat wcf_left = new WritableCellFormat(NormalFont); wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐 wcf_left.setAlignment(Alignment.LEFT); wcf_left.setWrap(true); // 是否换行

// 用于正文左 WritableCellFormat wcf_center = new WritableCellFormat(NormalFont); wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐 wcf_center.setAlignment(Alignment.CENTRE); wcf_center.setWrap(true); // 是否换行

// 用于正文右 WritableCellFormat wcf_right = new WritableCellFormat(NormalFont); wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐 wcf_right.setAlignment(Alignment.RIGHT); wcf_right.setWrap(false); // 是否换行

// 用于跨行 WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont); wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐 wcf_merge.setAlignment(Alignment.LEFT); wcf_merge.setWrap(true); // 是否换行

WritableCellFormat wcf_table = new WritableCellFormat(NormalFont); wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条 wcf_table.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐 wcf_table.setAlignment(Alignment.CENTRE); wcf_table.setBackground(Colour.GRAY_25); wcf_table.setWrap(true); // 是否换行

/** */ /** ************单元格格式设置完成****************** */ // 合并单元格,注意mergeCells(col0,row0,col1,row1) // --列从0开始,col1为你要合并到第几列,行也一样 sheet.mergeCells(0, 0, 6, 0);

sheet.addCell(new Label(0, 0, "怀化君源华天大酒店留言信息", wcf_title));

// sheet.mergeCells(0, 1, 1, 1);// sheet.mergeCells(2, 1, 5, 1);// sheet.addCell(new Label(0, 1, "", wcf_center));// sheet.addCell(new Label(2, 1, "姓名:" + "supercrsky",wcf_center)); // // sheet.mergeCells(0, 2, 1, 2); // sheet.mergeCells(2, 2, 3, 2); //// sheet.addCell(new Label(0, 2, "单位:", wcf_center));// sheet.addCell(new Label(2, 2, "ChinaLong", wcf_center));// sheet.addCell(new Label(4, 2, "薪水", wcf_center));// sheet.addCell(new Label(5, 2, "5000", wcf_center)); // // sheet.mergeCells(0, 3, 1, 3); // sheet.mergeCells(2, 3, 3, 3); // // sheet.addCell(new Label(0, 3, "性别:", wcf_center)); // sheet.addCell(new Label(2, 3, "男", wcf_center)); // sheet.addCell(new Label(4, 3, "婚否:", wcf_center)); // sheet.addCell(new Label(5, 3, "否", wcf_center)); // // sheet.mergeCells(0, 4, 1, 4); // sheet.mergeCells(2, 4, 3, 4); // // sheet.addCell(new Label(0, 4, "是否在职:", wcf_center)); // sheet.addCell(new Label(2, 4,"是", // wcf_center)); // sheet.addCell(new Label(4, 4,"工作经验:", wcf_center)); // sheet.addCell(new Label(5, 4, "4",wcf_center)); // // sheet.mergeCells(0, 5, 1, 5); // sheet.mergeCells(2, 5, 3, 5); // // sheet.addCell(new Label(0, 5, "保险费:", wcf_center)); // sheet.addCell(new Label(2, 5,"50", // wcf_center)); // sheet.addCell(new Label(4, 5, "保险金额:", wcf_center)); // sheet.addCell(new Label(5, 5, "50000", // wcf_center)); // // sheet.mergeCells(0, 6, 1, 6); // sheet.mergeCells(2, 6, 3, 6); // // sheet.addCell(new Label(0, 6, "工作地点:", wcf_center)); // sheet.addCell(new Label(2, 6, "北京", wcf_center)); // sheet.addCell(new Label(4, 6, "开心度：", wcf_center)); // sheet.addCell(new Label(5, 6, "一般", wcf_center));

// 另起一table

// sheet.mergeCells(0, 1, 8, 1);

// sheet.addCell(new Label(0, 1, "详细数据", wcf_tabletitle)); // table标题 sheet.addCell(new Label(0, 1, "序号", wcf_table)); sheet.addCell(new Label(1, 1, "姓名", wcf_table)); sheet.addCell(new Label(2, 1, "性别", wcf_table)); sheet.addCell(new Label(3, 1, "留言时间", wcf_table)); sheet.addCell(new Label(4, 1, "QQ号码", wcf_table)); sheet.addCell(new Label(5, 1, "E-mail", wcf_table)); sheet.addCell(new Label(6, 1, "留言信息", wcf_table)); // table内容 // 这里用你的dao// HibernateDAO dao = new HibernateDAO();// List list = dao.show_message();// System.out.println("数据行数:" + list.size());// for (int i = 0; i < list.size(); i++) {// // 对应你的vo类// TbMessage data = (TbMessage) list.get(i);//// sheet.addCell(new Label(0, 2 + i, String.valueOf(i + 1),// wcf_center));// sheet.addCell(new Label(1, 2 + i, data.getNickname(),// wcf_center));// sheet// .addCell(new Label(2, 2 + i, data.getGender(),// wcf_center));// sheet.addCell(new Label(3, 2 + i, String.valueOf(data// .getDatetime()), wcf_center));// sheet.addCell(new Label(4, 2 + i, data.getQq(), wcf_center));// sheet.addCell(new Label(5, 2 + i, data.getEmail(), wcf_center));// sheet// .addCell(new Label(6, 2 + i, data.getMessage(),// wcf_center));// } /** */ /** **********以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中******** */ workbook.write(); /** */ /** *********关闭文件************* */ workbook.close(); System.out.println("导出成功"); // 存放url地址 } catch (Exception e) { System.out.println("在输出到EXCEL的过程中出现错误，错误原因：" + e.toString()); }

}

public void testExcel2() {

try { /** */ /** **********创建工作簿************ */ WritableWorkbook workbook = Workbook.createWorkbook(new File( "d:/预定信息.xls")); /** */ /** **********创建工作表************ */ WritableSheet sheet = workbook.createSheet("留言信息", 0);

/** */ /** *********设置列宽**************** */ sheet.setColumnView(0, 5); // 第1列 sheet.setColumnView(1, 12); // 第2列 sheet.setColumnView(2, 10); sheet.setColumnView(3, 20); sheet.setColumnView(4, 10); sheet.setColumnView(5, 18); sheet.setColumnView(6, 18); sheet.setColumnView(7, 18); sheet.setColumnView(8, 22); sheet.setColumnView(9, 10); // 设置行高 sheet.setRowView(0, 1600, false); sheet.setRowView(1, 400, false); sheet.setRowView(7, 400, false); // 设置页边距 sheet.getSettings().setRightMargin(0.5); // 设置页脚 sheet.setFooter("", "", "测试页脚"); /** */ /** ************设置单元格字体************** */ // 字体 WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10); WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD); WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD); WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

/** */ /** ************单元格格式设置完成****************** */ // 合并单元格,注意mergeCells(col0,row0,col1,row1) // --列从0开始,col1为你要合并到第几列,行也一样 sheet.mergeCells(0, 0, 9, 0);

sheet.addCell(new Label(0, 0, "怀化君源华天大酒店预定信息", wcf_title));

// sheet.mergeCells(0, 1, 1, 1); // sheet.mergeCells(2, 1, 5, 1); // // sheet.addCell(new Label(0, 1, "", wcf_center)); // sheet.addCell(new Label(2, 1, "姓名:" + "supercrsky", // wcf_center)); // // sheet.mergeCells(0, 2, 1, 2); // sheet.mergeCells(2, 2, 3, 2); // // sheet.addCell(new Label(0, 2, "单位:", wcf_center)); // sheet.addCell(new Label(2, 2, "ChinaLong", wcf_center)); // sheet.addCell(new Label(4, 2, "薪水", wcf_center)); // sheet.addCell(new Label(5, 2, "5000", wcf_center)); // // sheet.mergeCells(0, 3, 1, 3); // sheet.mergeCells(2, 3, 3, 3); // // sheet.addCell(new Label(0, 3, "性别:", wcf_center)); // sheet.addCell(new Label(2, 3, "男", wcf_center)); // sheet.addCell(new Label(4, 3, "婚否:", wcf_center)); // sheet.addCell(new Label(5, 3, "否", wcf_center)); // // sheet.mergeCells(0, 4, 1, 4); // sheet.mergeCells(2, 4, 3, 4); // // sheet.addCell(new Label(0, 4, "是否在职:", wcf_center)); // sheet.addCell(new Label(2, 4,"是", // wcf_center)); // sheet.addCell(new Label(4, 4,"工作经验:", wcf_center)); // sheet.addCell(new Label(5, 4, "4",wcf_center)); // // sheet.mergeCells(0, 5, 1, 5); // sheet.mergeCells(2, 5, 3, 5); // // sheet.addCell(new Label(0, 5, "保险费:", wcf_center)); // sheet.addCell(new Label(2, 5,"50", // wcf_center)); // sheet.addCell(new Label(4, 5, "保险金额:", wcf_center)); // sheet.addCell(new Label(5, 5, "50000", // wcf_center)); // // sheet.mergeCells(0, 6, 1, 6); // sheet.mergeCells(2, 6, 3, 6); // // sheet.addCell(new Label(0, 6, "工作地点:", wcf_center)); // sheet.addCell(new Label(2, 6, "北京", wcf_center)); // sheet.addCell(new Label(4, 6, "开心度：", wcf_center)); // sheet.addCell(new Label(5, 6, "一般", wcf_center));

// 另起一table

// sheet.mergeCells(0, 1, 8, 1);

// sheet.addCell(new Label(0, 1, "详细数据", wcf_tabletitle)); // table标题 sheet.addCell(new Label(0, 1, "序号", wcf_table)); sheet.addCell(new Label(1, 1, "顾客姓名", wcf_table)); sheet.addCell(new Label(2, 1, "性别", wcf_table)); sheet.addCell(new Label(3, 1, "房间类型", wcf_table)); sheet.addCell(new Label(4, 1, "房间号", wcf_table)); sheet.addCell(new Label(5, 1, "入住时间", wcf_table)); sheet.addCell(new Label(6, 1, "离店时间", wcf_table)); sheet.addCell(new Label(7, 1, "顾客电话", wcf_table)); sheet.addCell(new Label(8, 1, "顾客邮箱", wcf_table)); sheet.addCell(new Label(9, 1, "入住情况", wcf_table));// // table内容// // 这里用你的dao// ManagerBookInfoDAO dao=new ManagerBookInfoDAO();// List list = dao.showBookInfo();// System.out.println("数据行数:" + list.size());// for (int i = 0; i < list.size(); i++) {// // 对应你的vo类// BookRoomInfo data = (BookRoomInfo) list.get(i);//// sheet.addCell(new Label(0, 2 + i, String.valueOf(i + 1),// wcf_center));// sheet.addCell(new Label(1, 2 + i, data.getName(),// wcf_center));// sheet// .addCell(new Label(2, 2 + i, data.getGender(),// wcf_center));// sheet// .addCell(new Label(3, 2 + i, data.getRoomType(),// wcf_center));// sheet// .addCell(new Label(4, 2 + i, data.getRoomId(),// wcf_center));// sheet.addCell(new Label(5, 2 + i, String.valueOf(data.getBegDate()).substring(0, 11)// , wcf_center));// sheet.addCell(new Label(6, 2 + i, String.valueOf(data.getEndDate()).substring(0, 11), wcf_center));// sheet.addCell(new Label(7, 2 + i, data.getTelephone(), wcf_center));// sheet// .addCell(new Label(8, 2 + i, data.getEmail(),// wcf_center));// // sheet// .addCell(new Label(9, 2 + i, data.getState(),// wcf_center));// } /** */ /** **********以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中******** */ workbook.write(); /** */ /** *********关闭文件************* */ workbook.close(); System.out.println("导出成功"); // 存放url地址 } catch (Exception e) { System.out.println("在输出到EXCEL的过程中出现错误，错误原因：" + e.toString()); }

} /** * function:导出store信息 * @return * @throws Exception */ public void testExcel3() throws Exception { try { /** */ /** **********创建工作簿************ */ WritableWorkbook workbook = Workbook.createWorkbook(new File( "d:/资产信息.xls")); /** */ /** **********创建工作表************ */ WritableSheet sheet = workbook.createSheet("资产信息", 0);

/** */ /** *********设置列宽**************** */ sheet.setColumnView(0, 5); // 第1列 sheet.setColumnView(1, 12); // 第2列 sheet.setColumnView(2, 10); sheet.setColumnView(3, 22); sheet.setColumnView(4, 15); sheet.setColumnView(5, 22); sheet.setColumnView(6, 18); sheet.setColumnView(7, 30); sheet.setColumnView(8, 15); sheet.setColumnView(9, 15); sheet.setColumnView(10, 20); // 设置行高 sheet.setRowView(0, 1600, false); sheet.setRowView(1, 400, false); sheet.setRowView(7, 400, false); // 设置页边距 sheet.getSettings().setRightMargin(0.5); // 设置页脚 sheet.setFooter("", "", "测试页脚"); /** */ /** ************设置单元格字体************** */ // 字体 WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10); WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD); WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD); WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

/** */ /** ************单元格格式设置完成****************** */ // 合并单元格,注意mergeCells(col0,row0,col1,row1) // --列从0开始,col1为你要合并到第几列,行也一样 sheet.mergeCells(0, 0, 10, 0); //总标题 sheet.addCell(new Label(0, 0, "it资产详细信息", wcf_title));

// table标题 sheet.addCell(new Label(0, 1, "序号", wcf_table)); sheet.addCell(new Label(1, 1, "受理人", wcf_table)); sheet.addCell(new Label(2, 1, "采购单号", wcf_table)); sheet.addCell(new Label(3, 1, "机器类型", wcf_table)); sheet.addCell(new Label(4, 1, "服务编号", wcf_table)); sheet.addCell(new Label(5, 1, "资产编号", wcf_table)); sheet.addCell(new Label(6, 1, "机器型号", wcf_table)); sheet.addCell(new Label(7, 1, "机器mac", wcf_table)); sheet.addCell(new Label(8, 1, "状态", wcf_table)); sheet.addCell(new Label(9, 1, "领用人", wcf_table)); sheet.addCell(new Label(10, 1, "领用时间", wcf_table)); // table内容 // 这里用你的dao// HibernateDAO dao = new HibernateDAO();// List list = dao.show_message();// System.out.println("数据行数:" + list.size());// for (int i = 0; i < list.size(); i++) {// // 对应你的vo类// TbMessage data = (TbMessage) list.get(i);//// sheet.addCell(new Label(0, 2 + i, String.valueOf(i + 1),// wcf_center));// sheet.addCell(new Label(1, 2 + i, data.getNickname(),// wcf_center));// sheet// .addCell(new Label(2, 2 + i, data.getGender(),// wcf_center));// sheet.addCell(new Label(3, 2 + i, String.valueOf(data// .getDatetime()), wcf_center));// sheet.addCell(new Label(4, 2 + i, data.getQq(), wcf_center));// sheet.addCell(new Label(5, 2 + i, data.getEmail(), wcf_center));// sheet// .addCell(new Label(6, 2 + i, data.getMessage(),// wcf_center));// } /** */ /** **********以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中******** */ workbook.write(); /** */ /** *********关闭文件************* */ workbook.close(); System.out.println("导出成功"); // 存放url地址 } catch (Exception e) { System.out.println("在输出到EXCEL的过程中出现错误，错误原因：" + e.toString()); } }

}

转载于:https://www.cnblogs.com/quanfu2326/p/4452517.html

相关资源：DirectX修复工具V4.0增强版

专利

最新回复(0)