package com.wanglin.jxl.operateExcel;
import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.Calendar;
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.DateFormat;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Number;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class CreateExcel {
private static final String PATH = "D:/TestCreateExcel.xls";
private static final String SHEET = "测试创建Excel";
//图片必须是png格式
private static final String IMAGEPATH = System.getProperty("user.dir")+"/IMG_987.png";
public CreateExcel() {
}
public static void createExcel() throws IOException, Exception {
WritableWorkbook wwb = null;
WritableSheet sheet = null;
wwb = Workbook.createWorkbook(new File(PATH));
sheet = wwb.createSheet(SHEET, 0);
// 定义格式
WritableFont writableFont = new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD);
writableFont.setColour(Colour.GREEN);
WritableCellFormat cellFormat = new WritableCellFormat(writableFont);
cellFormat.setAlignment(Alignment.LEFT);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 定义格式
NumberFormat numberFormat = new NumberFormat("#.##");
WritableFont writableFont2 = new WritableFont(WritableFont.ARIAL,14,WritableFont.NO_BOLD);
writableFont2.setColour(Colour.RED);
WritableCellFormat cellFormat2 = new WritableCellFormat(writableFont2,numberFormat);
cellFormat2.setAlignment(Alignment.RIGHT);
cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
// 定义格式
DateFormat dateFormat = new DateFormat("yyy-MM-dd hh:mm:ss sss");
WritableFont writableFont3 = new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD);
writableFont3.setColour(Colour.BLUE);
WritableCellFormat cellFormat3 = new WritableCellFormat(writableFont3,dateFormat);
cellFormat3.setAlignment(Alignment.CENTRE);
cellFormat3.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat3.setBorder(Border.ALL, BorderLineStyle.DOUBLE,Colour.RED);
Label label = new Label(0,0,"单元格1",cellFormat);
sheet.addCell(label);
Number number = new Number(1, 0, 3.14159,cellFormat2);
sheet.addCell(number);
DateTime dateTime = new DateTime(2, 0, Calendar.getInstance().getTime(),cellFormat3);
sheet.addCell(dateTime);
Number number2 = new Number(14, 0, 5.14,cellFormat2);
sheet.addCell(number2);
//公式计算值
Formula formula = new Formula(5,0,"SUM(B1,E1)",cellFormat2);
sheet.addCell(formula);
//添加图片
WritableImage image = new WritableImage(0,2,5,35,new File(IMAGEPATH));
sheet.addImage(image);
//设置超链接
WritableHyperlink hyperlink = new WritableHyperlink(6,0,new URL("http://www.hao123.com"));
hyperlink.setDescription("超链接");
sheet.addHyperlink(hyperlink);
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 20);
sheet.setRowView(0,500);
//合并单元格
sheet.mergeCells(2, 0, 3, 0);
wwb.write();
wwb.close();
}
public static void main(String [] args) {
try {
CreateExcel.createExcel();
} catch(IOException ioe) {
ioe.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
}
}
import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.Calendar;
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.DateFormat;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Number;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class CreateExcel {
private static final String PATH = "D:/TestCreateExcel.xls";
private static final String SHEET = "测试创建Excel";
//图片必须是png格式
private static final String IMAGEPATH = System.getProperty("user.dir")+"/IMG_987.png";
public CreateExcel() {
}
public static void createExcel() throws IOException, Exception {
WritableWorkbook wwb = null;
WritableSheet sheet = null;
wwb = Workbook.createWorkbook(new File(PATH));
sheet = wwb.createSheet(SHEET, 0);
// 定义格式
WritableFont writableFont = new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD);
writableFont.setColour(Colour.GREEN);
WritableCellFormat cellFormat = new WritableCellFormat(writableFont);
cellFormat.setAlignment(Alignment.LEFT);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 定义格式
NumberFormat numberFormat = new NumberFormat("#.##");
WritableFont writableFont2 = new WritableFont(WritableFont.ARIAL,14,WritableFont.NO_BOLD);
writableFont2.setColour(Colour.RED);
WritableCellFormat cellFormat2 = new WritableCellFormat(writableFont2,numberFormat);
cellFormat2.setAlignment(Alignment.RIGHT);
cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
// 定义格式
DateFormat dateFormat = new DateFormat("yyy-MM-dd hh:mm:ss sss");
WritableFont writableFont3 = new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD);
writableFont3.setColour(Colour.BLUE);
WritableCellFormat cellFormat3 = new WritableCellFormat(writableFont3,dateFormat);
cellFormat3.setAlignment(Alignment.CENTRE);
cellFormat3.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat3.setBorder(Border.ALL, BorderLineStyle.DOUBLE,Colour.RED);
Label label = new Label(0,0,"单元格1",cellFormat);
sheet.addCell(label);
Number number = new Number(1, 0, 3.14159,cellFormat2);
sheet.addCell(number);
DateTime dateTime = new DateTime(2, 0, Calendar.getInstance().getTime(),cellFormat3);
sheet.addCell(dateTime);
Number number2 = new Number(14, 0, 5.14,cellFormat2);
sheet.addCell(number2);
//公式计算值
Formula formula = new Formula(5,0,"SUM(B1,E1)",cellFormat2);
sheet.addCell(formula);
//添加图片
WritableImage image = new WritableImage(0,2,5,35,new File(IMAGEPATH));
sheet.addImage(image);
//设置超链接
WritableHyperlink hyperlink = new WritableHyperlink(6,0,new URL("http://www.hao123.com"));
hyperlink.setDescription("超链接");
sheet.addHyperlink(hyperlink);
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 20);
sheet.setRowView(0,500);
//合并单元格
sheet.mergeCells(2, 0, 3, 0);
wwb.write();
wwb.close();
}
public static void main(String [] args) {
try {
CreateExcel.createExcel();
} catch(IOException ioe) {
ioe.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
}
}
}