JEXCEL的使用

最新推荐文章于 2024-06-17 16:41:12 发布

wuhailin2005

最新推荐文章于 2024-06-17 16:41:12 发布

阅读量3k

点赞数

分类专栏： Java 文章标签： wcf string 工作 date null excel

Java 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

package excel.jxl;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Date;
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.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class JExcelUtils {
/**
* 生成Excel文件
* @param path 文件路径
* @param sheetName 工作表名称
* @param dataTitles 数据标题
*/
public void createExcelFile(String path,String sheetName,String[] dataTitles){
WritableWorkbook workbook;
try{
OutputStream os=new FileOutputStream(path);
workbook=Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表
//setRowView(int row, int height);--行高
//sheet.setRowView(0,100);
//setColumnView(int col,int width); --列宽
sheet.setColumnView(0,20);
Label label;
for (int i=0; i<dataTitles.length; i++){
//Label(列号,行号,内容,风格)
label = new Label(i, 0, dataTitles[i],getTitleCellFormat());
sheet.addCell(label);
}
//插入一行
insertRowData(sheet,1,new String[]{"200201001","张三","100","60","100","260"},getDataCellFormat(String.class));
//一个一个插入行
label = new Label(0, 2,"200201002",getDataCellFormat(String.class));
sheet.addCell(label);
label = new Label(1, 2,"李四",getDataCellFormat(String.class));
sheet.addCell(label);
insertOneCellData(sheet,2,2,70.5,getDataCellFormat(Double.class));
insertOneCellData(sheet,3,2,90.5,getDataCellFormat(Double.class));
insertOneCellData(sheet,4,2,60.5,getDataCellFormat(Double.class));
insertOneCellData(sheet,5,2,221.5,getDataCellFormat(Double.class));
//插入日期
mergeCellsAndInsertData(sheet, 0, 3, 5, 3, new Date(), getDataCellFormat(Date.class));
workbook.write();
workbook.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 插入一行数据
* @param sheet 工作表
* @param row 行号
* @param content 内容
* @param format 风格
*/
public void insertRowData(WritableSheet sheet,Integer row,String[] dataArr,WritableCellFormat format){
try{
Label label;
for(int i=0;i<dataArr.length;i++){
label = new Label(i,row,dataArr[i],format);
sheet.addCell(label);
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 插入单元格数据
* @param sheet
* @param col
* @param row
* @param data
*/
public void insertOneCellData(WritableSheet sheet,Integer col,Integer row,Object data,WritableCellFormat format){
try{
if(data instanceof Double){
jxl.write.Number labelNF = new jxl.write.Number(col,row,(Double)data,format);
sheet.addCell(labelNF);
}else if(data instanceof Boolean){
jxl.write.Boolean labelB = new jxl.write.Boolean(col,row,(Boolean)data,format);
sheet.addCell(labelB);
}else if(data instanceof Date){
jxl.write.DateTime labelDT = new jxl.write.DateTime(col,row,(Date)data,format);
sheet.addCell(labelDT);
}else{
Label label = new Label(col,row,data.toString(),format);
sheet.addCell(label);
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 合并单元格，并插入数据
* @param sheet
* @param col_start
* @param row_start
* @param col_end
* @param row_end
* @param data
* @param format
*/
public void mergeCellsAndInsertData(WritableSheet sheet,Integer col_start,Integer row_start,Integer col_end,Integer row_end,Object data, WritableCellFormat format){
try{
sheet.mergeCells(col_start,row_start,col_end,row_end);//左上角到右下角
insertOneCellData(sheet, col_start, row_start, data, format);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 得到数据表头格式
* @return
*/
public WritableCellFormat getTitleCellFormat(){
WritableCellFormat wcf = null;
try {
//字体样式
WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一个为是否italic
wf.setColour(Colour.RED);
wcf = new WritableCellFormat(wf);
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.GREY_25_PERCENT);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
/**
* 得到数据格式
* @return
*/
public WritableCellFormat getDataCellFormat(Class clazz){
WritableCellFormat wcf = null;
try {
//字体样式
if(clazz.getName().endsWith("Double")){//数字
NumberFormat nf = new NumberFormat("#.#");
wcf = new WritableCellFormat(nf);
}else if(clazz.getName().endsWith("Date")){//日期
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss");
wcf = new jxl.write.WritableCellFormat(df);
}else{
WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic
wcf = new WritableCellFormat(wf);
}
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);
wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);
wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);
//背景色
wcf.setBackground(Colour.WHITE);
wcf.setWrap(true);//自动换行
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}
/**
* 打开文件看看
* @param exePath
* @param filePath
*/
public void openExcel(String exePath,String filePath){
Runtime r=Runtime.getRuntime();
String cmd[]={exePath,filePath};
try{
r.exec(cmd);
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args){
String[] titles = {"学号","姓名","语文","数学","英语","总分"};
JExcelUtils jxl = new JExcelUtils();
String filePath = "E:/test.xls";
jxl.createExcelFile(filePath,"成绩单",titles);
jxl.openExcel("C:/Program Files/Microsoft Office/OFFICE11/EXCEL.EXE",filePath);
}
}