iLife's 博客http://blog.csdn.net/fei1502816
1.首先,创建一个可读写的工作簿(WritableWorkbook):
- WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test.xls"));
2.如果是想要修改一个已存在的excel工作簿,则需要先获得它的原始工作簿,再创建一个可读写的副本:
- Workbook wb = Workbook.getWorkbook(new File("src\\test\\test.xls")); // 获得原始文档
- WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test_modified.xls"),wb); // 创建一个可读写的副本
3.取得要操作的sheet,并对其进行相应的操作,如改名、合并单元格、设置列宽、行高等:
- sheet.mergeCells(0, 0, 4, 0); // 合并单元格
- sheet.setRowView(0, 600); // 设置行的高度
- sheet.setColumnView(0, 30); // 设置列的宽度
- sheet.setColumnView(1, 20); // 设置列的宽度
4.通过WritableFont、WritableCellFormat等对象可以设置单元格的字体、样式等外观:
- WritableSheet sheet = workbook.getSheet(0); WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
- 20,//WritableFont.DEFAULT_POINT_SIZE, // 字号
- WritableFont.NO_BOLD, // 粗体
- false, // 斜体
- UnderlineStyle.NO_UNDERLINE, // 下划线
- Colour.BLUE2, // 字体颜色
- ScriptStyle.NORMAL_SCRIPT);
- WritableCellFormat wcf = new WritableCellFormat(titleWf);
- wcf.setBackground(Colour.GRAY_25);// 设置单元格的背景颜色
- wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式
- wcf.setBorder(Border.ALL, BorderLineStyle.THICK); // 添加边框
- cell.setCellFormat(wcf);
- sheet.setName("修改后"); // 给sheet页改名
- workbook.removeSheet(2); // 移除多余的标签页
- workbook.removeSheet(3);
demo1:
- public String createExcel(String path,String filename,List list,TblKepuoaRecordDispatch dispatch) throws Exception{
- File file = new File(path+File.separator+filename+".xls" );
- WritableWorkbook wbook = null;
- try {
- wbook = Workbook.createWorkbook(file);
- // 建立excel文件
- String tmptitle = filename; // 标题
- WritableSheet wsheet = wbook.createSheet(filename, 0); // sheet名称
- // 设置excel标题
- WritableFont wfont = new WritableFont(WritableFont.createFont("宋体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
- WritableCellFormat wcfFC = new WritableCellFormat(wfont);
- wcfFC.setAlignment(Alignment.CENTRE); // 设置对齐方式
- wsheet.mergeCells(0, 0, 9, 0); // 合并单元格
- wsheet.addCell(new Label(0, 0, tmptitle, wcfFC));
- wfont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 13,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
- wcfFC = new WritableCellFormat(wfont);
- wcfFC.setBorder(Border.NONE,BorderLineStyle.NONE);
- wcfFC.setAlignment(Alignment.CENTRE); // 设置对齐方式
- wsheet.setColumnView(0, 6); // 设置列的宽度
- wsheet.setColumnView(1, 20); // 设置列的宽度
- wsheet.setColumnView(2, 25); // 设置列的宽度
- wsheet.setColumnView(3, 60); // 设置列的宽度
- wsheet.setColumnView(4, 10); // 设置列的宽度
- wsheet.setColumnView(5, 6); // 设置列的宽度
- wsheet.setColumnView(6, 6); // 设置列的宽度
- wsheet.setColumnView(7, 80); // 设置列的宽度
- wsheet.setColumnView(8, 20); // 设置列的宽度
- wsheet.setColumnView(9, 20); // 设置列的宽度
- // 开始生成主体内容
- wsheet.addCell(new Label(0, 1, "序号",wcfFC));
- wsheet.addCell(new Label(1, 1, "日期",wcfFC));
- wsheet.addCell(new Label(2, 1, "发文编号",wcfFC));
- wsheet.addCell(new Label(3, 1, "收文单位",wcfFC));
- wsheet.addCell(new Label(4, 1, "文件类型",wcfFC));
- wsheet.addCell(new Label(5, 1, "件数",wcfFC));
- wsheet.addCell(new Label(6, 1, "附件",wcfFC));
- wsheet.addCell(new Label(7, 1, "文件标题",wcfFC));
- wsheet.addCell(new Label(8, 1, "存档编号",wcfFC));
- wsheet.addCell(new Label(9, 1, "签字",wcfFC));
- wfont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 13,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
- wcfFC = new WritableCellFormat(wfont);
- wcfFC.setBorder(Border.NONE,BorderLineStyle.NONE);
- int j = 2;
- for (int i = 0; i < list.size(); i++) {
- dispatch=(TblKepuoaRecordDispatch) list.get(i);
- String uptime="";
- if(dispatch.getDispatchDate()!=null&&!"".equals(dispatch.getDispatchDate())){
- uptime=sdf2.format(dispatch.getDispatchDate());//时间格式化
- }
- wsheet.addCell(new Label(0, j, i+1+"",wcfFC));
- wsheet.addCell(new Label(1, j,uptime,wcfFC));
- wsheet.addCell(new Label(2, j,dispatch.getDispatchNo(),wcfFC));
- wsheet.addCell(new Label(3, j,dispatch.getDispatchOrgan(),wcfFC));
- /*if(dispatch!=null && dispatch.getDispatchOpreator()!=null && !"".equals(dispatch.getDispatchOpreator())){
- if(dispatch.getCode()!=null && !"".equals(dispatch.getCode())){
- wsheet.addCell(new Label(4, j,dispatch.getDispatchOpreator()+"-"+dispatch.getCode(),wcfFC));
- }else{
- wsheet.addCell(new Label(4, j,dispatch.getDispatchOpreator(),wcfFC));
- }
- }*/
- wsheet.addCell(new Label(4, j, dispatch.getDispatchType(),wcfFC));
- wsheet.addCell(new Label(5, j,String.valueOf(dispatch.getDispatchCount()),wcfFC));
- wsheet.addCell(new Label(6, j,String.valueOf(dispatch.getDispatchAccessoriescCount()),wcfFC));
- wsheet.addCell(new Label(7, j,dispatch.getDispatchName(),wcfFC));
- wsheet.addCell(new Label(8, j, dispatch.getArchiveNo(),wcfFC));
- j++;
- }
- } catch (IOException e) {
- // TODO Auto-generated catch block
- Loggers.info("DispatchAction 中 createExcel 方法:"+e.getMessage());
- e.printStackTrace();
- }finally{
- if(wbook != null){
- wbook.write(); // 写入文件
- wbook.close();
- }
- }
- // 主体内容生成结束
- return filename+".xls";
- }
下面是参考jxl api里那个例子写的,把我自己觉得常用的excel操作基本都包含了:
- package test;
- import java.io.File;
- import java.io.IOException;
- import java.net.MalformedURLException;
- import java.net.URL;
- import java.util.ArrayList;
- import java.util.Calendar;
- import java.util.Date;
- import jxl.CellType;
- import jxl.Workbook;
- import jxl.format.Alignment;
- import jxl.format.Border;
- import jxl.format.BorderLineStyle;
- import jxl.format.Colour;
- import jxl.format.ScriptStyle;
- import jxl.format.UnderlineStyle;
- import jxl.format.VerticalAlignment;
- import jxl.read.biff.BiffException;
- import jxl.write.Blank;
- import jxl.write.DateFormat;
- import jxl.write.DateFormats;
- import jxl.write.DateTime;
- import jxl.write.Formula;
- import jxl.write.Label;
- import jxl.write.Number;
- import jxl.write.NumberFormat;
- import jxl.write.WritableCell;
- import jxl.write.WritableCellFeatures;
- import jxl.write.WritableCellFormat;
- import jxl.write.WritableFont;
- import jxl.write.WritableHyperlink;
- import jxl.write.WritableImage;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- import jxl.write.WriteException;
- /**
- *
- * @author why
- *
- */
- public class ExcelTest {
- /**
- * @param args
- * @throws IOException
- * @throws BiffException
- * @throws WriteException
- */
- public static void main(String[] args) throws IOException, BiffException, WriteException {
- Workbook wb = Workbook.getWorkbook(new File("src\\test\\test.xls")); // 获得原始文档
- WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test_modified.xls"),wb); // 创建一个可读写的副本
- /**
- * 定义与设置Sheet
- */
- WritableSheet sheet = workbook.getSheet(0);
- sheet.setName("修改后"); // 给sheet页改名
- workbook.removeSheet(2); // 移除多余的标签页
- workbook.removeSheet(3);
- sheet.mergeCells(0, 0, 4, 0); // 合并单元格
- sheet.setRowView(0, 600); // 设置行的高度
- sheet.setColumnView(0, 30); // 设置列的宽度
- sheet.setColumnView(1, 20); // 设置列的宽度
- WritableCell cell = null;
- WritableCellFormat wcf = null;
- Label label = null;
- WritableCellFeatures wcfeatures = null;
- // 更改标题字体
- cell = sheet.getWritableCell(0,0);
- WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
- 20,//WritableFont.DEFAULT_POINT_SIZE, // 字号
- WritableFont.NO_BOLD, // 粗体
- false, // 斜体
- UnderlineStyle.NO_UNDERLINE, // 下划线
- Colour.BLUE2, // 字体颜色
- ScriptStyle.NORMAL_SCRIPT);
- wcf = new WritableCellFormat(titleWf);
- wcf.setBackground(Colour.GRAY_25);// 设置单元格的背景颜色
- wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式
- wcf.setBorder(Border.ALL, BorderLineStyle.THICK); // 添加边框
- cell.setCellFormat(wcf);
- // 将B3的字体改为仿宋_GB2312
- cell = sheet.getWritableCell(1,2);
- WritableFont fs = new WritableFont(WritableFont.createFont("仿宋_GB2312"),
- 11);
- wcf = new WritableCellFormat(fs);
- cell.setCellFormat(wcf);
- // 将B4的字号改为20
- cell = sheet.getWritableCell(1,3);
- WritableFont size20 = new WritableFont(WritableFont.createFont("宋体"),
- 20);
- wcf = new WritableCellFormat(size20);
- cell.setCellFormat(wcf);
- // 将B5的字体改为加粗
- cell = sheet.getWritableCell(1,4);
- WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),
- 11,
- WritableFont.BOLD);
- wcf = new WritableCellFormat(bold);
- cell.setCellFormat(wcf);
- // 将B6的字体改为倾斜
- cell = sheet.getWritableCell(1,5);
- WritableFont italic = new WritableFont(WritableFont.createFont("宋体"),
- 11,
- WritableFont.NO_BOLD,
- true);
- wcf = new WritableCellFormat(italic);
- cell.setCellFormat(wcf);
- // 将B7字体加下划线
- cell = sheet.getWritableCell(1,6);
- WritableFont underline = new WritableFont(WritableFont.createFont("宋体"),
- 11,
- WritableFont.NO_BOLD,
- false,
- UnderlineStyle.SINGLE);
- wcf = new WritableCellFormat(underline);
- cell.setCellFormat(wcf);
- // 将B8的文字改为“待修改文字-已修改”
- cell = sheet.getWritableCell(1,7);
- if (cell.getType() == CellType.LABEL)
- {
- Label lc = (Label) cell;
- lc.setString(lc.getString() + " - 已修改");
- }
- // 将B9文字对齐方式改为垂直居中、右对齐
- cell = sheet.getWritableCell(1,8);
- WritableFont align = new WritableFont(WritableFont.createFont("宋体"),
- 11);
- wcf = new WritableCellFormat(align);
- wcf.setAlignment(Alignment.RIGHT); // 设置为右对齐
- wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置为垂直居中
- cell.setCellFormat(wcf);
- // 将E3文字改为自动换行
- cell = sheet.getWritableCell(4,2);
- WritableFont justify = new WritableFont(WritableFont.createFont("宋体"),
- 11);
- wcf = new WritableCellFormat(justify);
- wcf.setAlignment(Alignment.JUSTIFY);
- cell.setCellFormat(wcf);
- // 将B12的数字有效位数从5位改为7位
- cell = sheet.getWritableCell(1,11);
- NumberFormat sevendps = new NumberFormat("#.0000000");
- wcf = new WritableCellFormat(sevendps);
- cell.setCellFormat(wcf);
- // 将B13改为4位科学计数法表示
- cell = sheet.getWritableCell(1,12);
- NumberFormat exp4 = new NumberFormat("0.####E0");
- wcf = new WritableCellFormat(exp4);
- cell.setCellFormat(wcf);
- // 将B14改为默认数字表示
- cell = sheet.getWritableCell(1,13);
- cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
- // 将B15数字类型的值17改为22
- cell = sheet.getWritableCell(1,14);
- if (cell.getType() == CellType.NUMBER)
- {
- Number n = (Number) cell;
- n.setValue(42);
- }
- // 将B16的值2.71进行加法运算2.71 + 0.1
- cell = sheet.getWritableCell(1,15);
- if (cell.getType() == CellType.NUMBER)
- {
- Number n = (Number) cell;
- n.setValue(n.getValue() + 0.1);
- }
- // 将B19日期格式改为默认
- cell = sheet.getWritableCell(1,18);
- wcf = new WritableCellFormat(DateFormats.FORMAT9);
- cell.setCellFormat(wcf);
- // 将B20日期格式改为dd MMM yyyy HH:mm:ss
- cell = sheet.getWritableCell(1,19);
- DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
- wcf = new WritableCellFormat(df);
- cell.setCellFormat(wcf);
- // 将B21的日期设置为 2011-6-1 11:18:50
- cell = sheet.getWritableCell(1,20);
- if (cell.getType() == CellType.DATE)
- {
- DateTime dt = (DateTime) cell;
- Calendar cal = Calendar.getInstance();
- cal.set(2011, 5, 1, 11, 18, 50);
- Date d = cal.getTime();
- dt.setDate(d);
- }
- // 将B24文字添加链接http://www.baidu.com
- WritableHyperlink link = new WritableHyperlink(1, 23, new URL("http://www.baidu.com"));
- sheet.addHyperlink(link);
- // 更改URL链接
- WritableHyperlink hyperlinks[] = sheet.getWritableHyperlinks();
- for (int i = 0; i < hyperlinks.length; i++) {
- WritableHyperlink wh = hyperlinks[i];
- if (wh.getColumn() == 1 && wh.getRow() == 24) {
- // 将B25文字链接取消
- sheet.removeHyperlink(wh,true);//true:保留文字;false:删除文字
- }else if(wh.getColumn() == 1 && wh.getRow() == 25){
- try {
- // 将B26链接更改为http://wuhongyu.javaeye.com
- wh.setURL(new URL("http://wuhongyu.javaeye.com"));
- } catch (MalformedURLException e) {
- e.printStackTrace();
- }
- }
- }
- // 利用公式取得B29、B30的值
- Formula f1 = new Formula(1, 28, "SUM(C29:D29)");
- sheet.addCell(f1);
- Formula f2 = new Formula(1, 29, "AVERAGE(C30:G30)");
- sheet.addCell(f2);
- // 在B32处添加图片,图片大小占10行3列,只支持png格式
- File file = new File("d:\\shu05.png");
- WritableImage image = new WritableImage(1, 31, 3, 10, file);
- sheet.addImage(image);
- // 在A44出添加内容"Added drop down validation",并为其添加注释
- label = new Label(0, 43, "Added drop down validation");
- wcfeatures = new WritableCellFeatures();
- wcfeatures.setComment("右边列是个下拉列表");
- label.setCellFeatures(wcfeatures);
- sheet.addCell(label);
- // 在B44处添加一个下拉列表并添加注释
- Blank b = new Blank(1, 43);
- wcfeatures = new WritableCellFeatures();
- ArrayList al = new ArrayList();
- al.add("why");
- al.add("landor");
- al.add("tjm");
- wcfeatures.setDataValidationList(al);
- wcfeatures.setComment("这是一个注释");
- b.setCellFeatures(wcfeatures);
- sheet.addCell(b);
- // 为A46添加注释。
- // 此处比较麻烦,试了多次发现必须将cell强制类型转换、添加CellFeatures再修改注释才可用,不知有没有更好的办法。
- cell = sheet.getWritableCell(0,45);
- wcfeatures = new WritableCellFeatures();
- wcfeatures.setComment("这个注释不会被显示,删了这行还不行,MD");
- cell.setCellFeatures(wcfeatures);
- label = (Label) cell;
- // label.setCellFeatures(wcfeatures);// 直接这样写会报一个警告(“注释已存在”),但那个注释仍会被显示。
- label.addCellFeatures();
- label.getWritableCellFeatures().setComment("终于加上注释了,哈哈哈哈");
- // if (cell instanceof Number) {
- // Number num = (Number) cell;
- // num.setCellFeatures(wcfeatures);
- // } else if (cell instanceof jxl.write.Boolean) {
- // jxl.write.Boolean bool = (jxl.write.Boolean) cell;
- // bool.setCellFeatures(wcfeatures);
- // } else if (cell instanceof jxl.write.DateTime) {
- // jxl.write.DateTime dt = (jxl.write.DateTime) cell;
- // dt.setCellFeatures(wcfeatures);
- // } else {
- // Label _label = (Label) cell;
- // _label.setCellFeatures(wcfeatures);
- // }
- workbook.write();
- workbook.close();
- wb.close();
- }
- }
http://www.andykhan.com/jexcelapi/download.html Jxl--jar包下载地址