package cn.com.burgeon.excel;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class Jxltest {
public void crExcel() {
try {
WritableWorkbook wwb = Workbook.createWorkbook(new File(
"d:/test.xls"));
// 定义只读
// wwb.setProtected(true);
WritableSheet wa=null;
int sheetcount=200;
int allsheetcount=1001;
int sheetindex=0;
if(allsheetcount%sheetcount!=0){
sheetindex=allsheetcount/sheetcount+1;
}else {
sheetindex=allsheetcount/sheetcount;
}
for (int j = 0; j < sheetindex; j++) {
wa = wwb.createSheet("订货会款"+j, j);
// 设置宽和高
// wa.setColumnView(0, 18);
// wa.setRowView(0, 280);
// 合并单元格注意mergeCells(col0,row0,col1,row1) --列从0开始,col1为你要合并到第几列,行也一样
// wa.setProtected(false);
// 设置 字体,大小,颜色,斜体,黑体
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setAlignment(Alignment.CENTRE);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
// 设置垂直居上
// wa.setColumnView(1,19);
/*
* Label label4=new Label(3,1,"39",wcf); Label label5=new
* Label(4,1,"40",wcf); Label label6=new Label(5,1,"41",wcf); Label
* label7=new Label(6,1,"42",wcf); Label label8=new
* Label(7,1,"43",wcf); wa.addCell(label4); wa.addCell(label5);
* wa.addCell(label6); wa.addCell(label7); wa.addCell(label8);
*/
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//先循环上面四列,若是4的倍数,换行
int rowscount=0;//若为4的倍数,换行,rowcount+1
int sheetSize=allsheetcount-j*200;
if(sheetSize>sheetcount){
sheetSize=sheetcount;
}
for(int i=0;i
if(i!=0&&i%4==0){
rowscount+=1;
}
int colsindex=i%4;//得到列索引 cols纵 rows横 cols终止位置 rows 终止位置
int rows=(rowscount*8)>0?(rowscount*8):0; //行坐标
int cols=(colsindex*5)>0?(colsindex*5):0; //列坐标
wa.mergeCells(cols+0, rows+0, cols+0, rows+0);
wa.mergeCells(cols+1, rows+0, cols+2, rows+0);
wa.mergeCells(cols+3, rows+0, cols+3, rows+0);
wa.mergeCells(cols+0, rows+1, cols+0, rows+1);
wa.mergeCells(cols+1, rows+1, cols+2, rows+1);
wa.mergeCells(cols+3, rows+1, cols+3, rows+1);
wa.mergeCells(cols+4, rows+1, cols+4, rows+1);
wa.mergeCells(cols+0, rows+2, cols+4, rows+7);
Label label = new Label(cols+0, rows+0, "款号", wcf);
wa.addCell(label);
Label label1 = new Label(cols+1, rows+0, "品名", wcf);
wa.addCell(label1);
Label label2 = new Label(cols+3, rows+0, "色号", wcf);
wa.addCell(label2);
Label label3 = new Label(cols+4, rows+0, "零售价", wcf);
wa.addCell(label3);
Label label4 = new Label(cols+0, rows+1, "DC3-1", wcf);
wa.addCell(label4);
Label label5 = new Label(cols+1, rows+1, "女款", wcf);
wa.addCell(label5);
Label label6 = new Label(cols+3, rows+1, "-", wcf);
wa.addCell(label6);
Label label7 = new Label(cols+4, rows+1, "880.00", wcf);
wa.addCell(label7);
File image = new File("d:/as0011.png");
// 只支持png格式的 图形 参数 分别表示 开始列 行 宽 高
WritableImage wimage = new WritableImage(cols+1, rows+2, 2, 6, image);
WritableCellFormat wcf1 = new WritableCellFormat();
wcf1.setBorder(Border.ALL, BorderLineStyle.THIN);
wa.getWritableCell(0, 3).setCellFormat(wcf1);
wa.addImage(wimage);
}
}
/* wa.mergeCells(0, 0, 0, 0);
wa.mergeCells(1, 0, 2, 0);
wa.mergeCells(3, 0, 3, 0);
wa.mergeCells(0, 1, 0, 1);
wa.mergeCells(1, 1, 2, 1);
wa.mergeCells(3, 1, 3, 1);
wa.mergeCells(4, 1, 4, 1);
wa.mergeCells(0, 2, 4, 7);
Label label = new Label(0, 0, "款号", wcf);
wa.addCell(label);
Label label1 = new Label(1, 0, "品名", wcf);
wa.addCell(label1);
Label label2 = new Label(3, 0, "色号", wcf);
wa.addCell(label2);
Label label3 = new Label(4, 0, "零售价", wcf);
wa.addCell(label3);
Label label4 = new Label(0, 1, "DC3-1", wcf);
wa.addCell(label4);
Label label5 = new Label(1, 1, "女款", wcf);
wa.addCell(label5);
Label label6 = new Label(3, 1, "-", wcf);
wa.addCell(label6);
Label label7 = new Label(4, 1, "880.00", wcf);
wa.addCell(label7);
File image = new File("d:/as0011.png");
// 只支持png格式的 图形 参数 分别表示 开始列 行 宽 高
WritableImage wimage = new WritableImage(1, 2, 2, 6, image);
WritableCellFormat wcf1 = new WritableCellFormat();
wcf1.setBorder(Border.ALL, BorderLineStyle.THIN);
wa.getWritableCell(0, 3).setCellFormat(wcf1);
wa.addImage(wimage);*/
wwb.write();
wwb.close();
} catch (IOException ie) {
ie.printStackTrace();
} catch (WriteException we) {
we.printStackTrace();
}
}
public static void main(String[] args) {
new Jxltest().crExcel();
}
}