java jxl 设置每页打印范围_jxl操作excel（每页200个，每行4个）

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();

}

}