要往xls文件里面写入数据的时候需要注意的是第一要新建一个xls文件
OutputStream os=new FileOutputStream("c://excel2.xls");
再建完这个文件的时候再建立工作文件
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(os));
如果这个文件已经存在,那么我们可以在这个文件里面加入一个sheet为了和以前的数据进行分开;
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);
在createSheet方法里前面的参数是sheet名,后面是要操作的sheet号
接下来就可以往这个文件里面写入数据了
写入数据的时候注意的格式
(1)添加的字体样式
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
WritableFont()方法里参数说明:
这个方法算是一个容器,可以放进去好多属性
第一个: TIMES是字体大小,他写的是18
第二个: BOLD是判断是否为斜体,选择true时为斜体
第三个: ARIAL
第四个: UnderlineStyle.NO_UNDERLINE 下划线
第五个: jxl.format.Colour.RED 字体颜色是红色的
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell",wcfF);
ws.addCell(labelC);
在Label()方法里面有三个参数
第一个是代表列数,
第二是代表行数,
第***要写入的内容
第四个是可选项,是输入这个label里面的样式
然后通过写sheet的方法addCell()把内容写进sheet里面。
(2)添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
(3)添加Number对象
(3.1)显示number对象数据的格式
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);
Number()方法参数说明:
前两上表示输入的位置
第三个表示输入的内容
(4)添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
(5)添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT);
DateTime()方法的参数说明
前两个表示输入的位置
第三个表示输入的当前时间
(6)添加带有formatting的DateFormat对象
这个显示当前时间的所有信息,包括年月日小时分秒
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF);
(7)添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
import="jxl.format.*
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.GREEN);
(8)设置单元格样式
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
wcfFC.setBackGround(jxl.format.Colour.RED);//设置单元格的颜色为红色
wcfFC = new jxl.write.Label(6,0,"i love china",wcfFC);
Jxl在写excel文件时使用的方法比较怪,也可以说jxl不支持修改excel文件。它的处理方式是每次打开旧excel文件,然后创建一个该excel文件的可写的副本,所有的修改都是在这个副本上做的。下面是一个例子。
package net.htjs.util;
import java.io.*;
import jxl.*;
public class ReadExcel
{
public String[][] readExcel(String fileNameTemp)
{
String[][] strTemp = null;
try
{
//构建Workbook对象, 只读Workbook对象
//直接从本地文件创建Workbook
//从输入流创建Workbook
InputStream is = new FileInputStream(fileNameTemp);
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0);
int rows=rs.getRows();
int cols=rs.getColumns();
//System.out.println(rows+" "+cols);
strTemp = new String[rows][cols];
for(int i=0;i<rows;i++)
{
for(int j=0;j<cols;j++)
{
Cell ctemp = rs.getCell(j,i);
strTemp[i][j] = ctemp.getContents();
}
}
rwb.close();
}catch(Exception ex){
ex.printStackTrace();
}
return strTemp;
}
}
package com.test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
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.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class WriteExcelTest {
static HashMap map = new HashMap();
public static void main(String[] args) {
try {
// copyDateFormat(new File("c://a.xls"), 0, "c://copy of a.xls");
writeExcelUseFormat("c://format.xls","test");
// buildNewFormTemplete(new File("c:/templete.xls"),new File(
// "c:/buildNewFormTemplete.xls"));
// modifyDirectly1(new File("c:/templete.xls"));
// modifyDirectly2(new File("c:/templete.xls"));
//copyDateAndFormat(new File("c:/a.xls"), 0, "c:/a2.xls");
} catch (Exception e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
public static void modifyDirectly2(File inputFile) throws Exception {
Workbook w1 = Workbook.getWorkbook(inputFile);
WritableWorkbook w2 = Workbook.createWorkbook(inputFile, w1);
WritableSheet sheet = w2.getSheet(0);
WritableCell cell = null;
CellFormat cf = null;
// 加粗
cell = sheet.getWritableCell(0, 0);
WritableFont bold = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
cf = new WritableCellFormat(bold);
cell.setCellFormat(cf);
// 设置下划线
cell = sheet.getWritableCell(0, 1);
WritableFont underline = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false,
UnderlineStyle.SINGLE);
cf = new WritableCellFormat(underline);
cell.setCellFormat(cf);
// 直截添加可以覆盖掉
setCellValueDirectly(sheet, sheet.getCell(0, 2), new Double(4),
CellType.NUMBER);
w2.write();
w2.close();
}
public static void modifyDirectly1(File file) {
try {
// Excel获得文件
Workbook wb = Workbook.getWorkbook(file);
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(file, wb);
WritableSheet sheet0 = book.getSheet(0);
sheet0.addCell(new Label(0, 1, "陈小稳"));
// 添加一个工作表
WritableSheet sheet = book.createSheet(" 第二页 ", 1);
sheet.addCell(new Label(0, 0, " 第二页的测试数据 "));
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
public static void buildNewFormTemplete(File inputFile, File outputFile) {
try {
// Excel获得文件
Workbook wb = Workbook.getWorkbook(inputFile);
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(outputFile, wb);
WritableSheet sheet0 = book.getSheet(0);
sheet0.addCell(new Label(0, 1, "陈小稳"));
// 添加一个工作表
WritableSheet sheet = book.createSheet(" 第二页 ", 1);
sheet.addCell(new Label(0, 0, " 第二页的测试数据 "));
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
public static void copyDateAndFormat(File inputFile,
int inputFileSheetIndex, String outputFilePath) throws Exception {
Workbook book = null;
Cell cell = null;
// 1.避免乱码的设置
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh", "CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
book = Workbook.getWorkbook(inputFile, setting);
Sheet readonlySheet = book.getSheet(inputFileSheetIndex);
OutputStream os = new FileOutputStream(outputFilePath);// 输出的Excel文件URL
WritableWorkbook wwb = Workbook.createWorkbook(os);// 创建可写工作薄
WritableSheet writableSheet = wwb.createSheet(readonlySheet.getName(),
0);// 创建可写工作表
// 2.誊写不同数据格式的数据
for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
cell = readonlySheet.getCell(colIndex, rowIndex);
// A2B2为合并的单元格,A2有内容,B2为空
// if(colIndex == 0 && rowIndex == 1){
// System.out.println(colIndex + "," + rowIndex + " type:" +
// cell.getType() +" :" + cell.getContents());
// }
// 【有各种设置格式】
if (cell.getType() == CellType.DATE
|| cell.getType() == CellType.DATE_FORMULA) {
writableSheet.addCell(new jxl.write.DateTime(colIndex,
rowIndex, ((DateCell) cell).getDate(),
new jxl.write.WritableCellFormat(cell
.getCellFormat())));
} else if (cell.getType() == CellType.NUMBER
|| cell.getType() == CellType.NUMBER_FORMULA) {
writableSheet.addCell(new jxl.write.Number(colIndex,
rowIndex, ((jxl.NumberCell) cell).getValue(),
new jxl.write.WritableCellFormat(cell
.getCellFormat())));
} else if (cell.getType() == CellType.EMPTY) {
// 空的以及合并单元格中第一列外的
// System.out.println("EMPTY:"+cell.getContents());
// System.err.println("空单元格 at " + colIndex + "," + rowIndex
// +" content:" + cell.getContents());
} else if (cell.getType() == CellType.LABEL
|| cell.getType() == CellType.STRING_FORMULA) {
writableSheet.addCell(new Label(colIndex, rowIndex, cell
.getContents(), new jxl.write.WritableCellFormat(
cell.getCellFormat())));
} else {
System.err.println("其它单元格类型:" + cell.getType() + " at "
+ colIndex + "," + rowIndex + " content:"
+ cell.getContents());
}
// if(cell.getType() == CellType.STRING_FORMULA){
// System.err.println(colIndex + "," + rowIndex +":" +
// cell.getContents() +" type:" + cell.getType());
// }
}
}
// 3.处理合并单元格的事情(复制合并单元格格式)
Range[] range = readonlySheet.getMergedCells();
for (int i = 0; i < range.length; i++) {
// System.out.println("第"+i+"处合并的单元格:"
// +",getTopLeft="+range[i].getTopLeft().getColumn()
// +","+range[i].getTopLeft().getRow()
// +",getBottomRight="+range[i].getBottomRight().getColumn()
// +","+range[i].getBottomRight().getRow()
// );
// topleftXIndex, topleftYIndex, bottomRightXIndex,
// bottomRightYIndex
writableSheet.mergeCells(range[i].getTopLeft().getColumn(),
range[i].getTopLeft().getRow(), range[i].getBottomRight()
.getColumn(), range[i].getBottomRight().getRow());
}
// 4.设置行列高宽
for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
writableSheet.setColumnView(colIndex, readonlySheet
.getColumnView(colIndex));
}
for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
writableSheet.setRowView(rowIndex, readonlySheet
.getRowView(rowIndex));
}
wwb.write();
wwb.close();
os.close();
}
public static void writeExcelUseFormat(String outputFilePath,
String outputFileSheetName) throws Exception {
OutputStream os = new FileOutputStream(outputFilePath);// 输出的Excel文件URL
WritableWorkbook wwb = Workbook.createWorkbook(os);// 创建可写工作薄
WritableSheet sheet = wwb.createSheet(outputFileSheetName, 0);// 创建可写工作表
sheet.addCell(new Label(0, 0, "号码"));
sheet.addCell(new Label(1, 0, "有效期"));
// 1.写入时间的数据格式
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(
df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 1, new Date(),
wcfDF); // 自定义格式
sheet.addCell(labelDTF);
// 2.字体样式
// WritableFont()方法里参数说明:
// 这个方法算是一个容器,可以放进去好多属性
// 第一个: TIMES是字体大小,他写的是18
// 第二个: BOLD是判断是否为斜体,选择true时为斜体
// 第三个: ARIAL
// 第四个: UnderlineStyle.NO_UNDERLINE 下划线
// 第五个: jxl.format.Colour.RED 字体颜色是红色的
jxl.write.WritableFont wf = new jxl.write.WritableFont(
WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
wcfF.setWrap(true);// 自动换行
wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 把水平对齐方式指定为居中
wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 把垂直对齐方式指定为居中
jxl.write.Label labelC = new jxl.write.Label(0, 1,
"This is a Label cell", wcfF);
sheet.addCell(labelC);
// 3.添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(0, 2, 3.1415926, wcfN);
sheet.addCell(labelNF);
// 4.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 3, false);
sheet.addCell(labelB);
// 5.设置一个注解
WritableCellFeatures cellFeatures = new WritableCellFeatures();
cellFeatures.setComment("添加Boolean对象");
labelB.setCellFeatures(cellFeatures);
// 6.单元格内换行
WritableCellFormat wrappedText = new WritableCellFormat(
WritableWorkbook.ARIAL_10_PT);
wrappedText.setWrap(true);// 可换行的label样式
Label label = new Label(4, 0, "测试,/012测试。。。", wrappedText); // "/012"强制换行
sheet.addCell(label);
// 7.数字的公式计算
Number n = new jxl.write.Number(0, 9, 4.5);// A10
sheet.addCell(n);
n = new Number(1, 9, 8);// B10
sheet.addCell(n);
NumberFormat dp3 = new NumberFormat("#.###"); // 设置单元格里面的数字格式
WritableCellFormat dp3cell = new WritableCellFormat(dp3);
dp3cell.setWrap(true);
Formula f = new Formula(2, 9, "(a10+b10)/2", dp3cell); // 设置C10公式
sheet.addCell(f);
f = new Formula(3, 9, "SUM(A10:B10)", dp3cell);// 设置D10公式
sheet.addCell(f);
// 8.设置sheet的样式
sheet.getSettings().setProtected(true); // 设置xls的保护,单元格为只读的
sheet.getSettings().setPassword("123"); // 设置xls的密码
sheet.getSettings().setDefaultColumnWidth(10); // 设置列的默认宽度,2cm左右
sheet.setRowView(3, 200);// 设置第4行高度
sheet.setRowView(2, false);// 这样可以自动把行高扩展
sheet.setColumnView(0, 300);// 设置第1列宽度,6cm左右
sheet.mergeCells(0, 5, 1, 7);// 合并单元格:合并A6B8也就是1列6行 与 2列7行之间的矩形
// 9.设置边框
drawRect(sheet, 5, 6, 7, 6, BorderLineStyle.THICK, Colour.BLACK, null);
sheet.mergeCells(1, 2, 3, 3);
wwb.write();
wwb.close();
os.close();
}
public static void drawRect(WritableSheet sheet, int x, int y, int width,
int height, BorderLineStyle style, Colour BorderColor,
Colour bgColor) throws WriteException {
for (int w = 0; w < width; w++) {
for (int h = 0; h < height; h++) {
WritableCellFormat alignStyle = new WritableCellFormat(); // 单元格样式
alignStyle.setAlignment(Alignment.CENTRE); // 设置对齐方式
alignStyle.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
if (h == 0)// 画上
alignStyle.setBorder(Border.TOP, style, BorderColor);// 设置边框的颜色和样式
if (w == 0)// 画左
alignStyle.setBorder(Border.LEFT, style, BorderColor);// 设置边框的颜色和样式
if (w == width - 1)// 画右
alignStyle.setBorder(Border.RIGHT, style, BorderColor);// 设置边框的颜色和样式
if (h == height - 1)// 画下
alignStyle.setBorder(Border.BOTTOM, style, BorderColor);// 设置边框的颜色和样式
// drawLine(sheet, x, y, Border.BOTTOM);
if (bgColor != null)
alignStyle.setBackground(bgColor); // 背静色
Label mergelabel = new Label(x, y, "", alignStyle);
// topleftXIndex, topleftYIndex, bottomRightXIndex,
// bottomRightYIndex
// sheet.mergeCells(2, 5, 10, 10);
sheet.addCell(mergelabel);
y++;
}
y -= height;
x++;
}
}
public static ArrayList<String> sampleReadExcel(File inputFile,
int inputFileSheetIndex) throws Exception {
ArrayList<String> list = new ArrayList<String>();
Workbook book = null;
Cell cell = null;
// 避免乱码的设置
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh", "CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
book = Workbook.getWorkbook(inputFile, setting);
Sheet sheet = book.getSheet(inputFileSheetIndex);
for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {// Excel第一行为表头,因此J初值设为1
for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {// 只需从Excel中取出2列
cell = sheet.getCell(colIndex, rowIndex);
list.add(cell.getContents());
}
}
// 【问题:如果在实际部署的时候没有写下面这句是否会导致不断消耗掉服务器的内存?jxl里面有个ReadWrite.java没有关闭读的,只关闭了写的】
book.close();
return list;
}
public static void setCellValueDirectly(WritableSheet sheet, Cell cell,
Object newValue, CellType type) throws Exception {
if (type == CellType.DATE || type == CellType.DATE_FORMULA) {
sheet.addCell(new jxl.write.DateTime(cell.getColumn(), cell
.getRow(), (Date) newValue,
new jxl.write.WritableCellFormat(cell.getCellFormat())));
} else if (type == CellType.NUMBER || type == CellType.NUMBER_FORMULA) {
sheet.addCell(new jxl.write.Number(cell.getColumn(), cell.getRow(),
((Double) newValue).doubleValue(),
new jxl.write.WritableCellFormat(cell.getCellFormat())));
} else if (type == CellType.LABEL || type == CellType.STRING_FORMULA) {
sheet.addCell(new Label(cell.getColumn(), cell.getRow(),
(String) newValue, new jxl.write.WritableCellFormat(cell
.getCellFormat())));
} else {
throw new Exception("不支持的其它单元格类型:" + type);
// System.err.println("不支持的其它单元格类型:" + cell.getType() + " at " +
// cell.getColumn() + "," + cell.getRow() +" current content:" +
// cell.getContents());
}
}
}
jxl打印设置
java报表JXL和POI打印设置
JXL的打印设置在jxl.SheetSettings这个类中,我们可以通过方法Sheet(或者WritableSheet)#getSettings()获取。
1.页面
1.1方向
SheetSetting#setOrientation(PageOrientation po);
参数: PageOrientation#LANDSCAPE 横向打印
PageOrientation# PORTRAIT 纵向打印
1.2缩放
1.2.1缩放比例(A) SheetSetting #setScaleFactor (int);百分比形式
1.2.2调整(F)
页宽 SheetSetting #setFitWidth(int);
页高 SheetSetting #setFitHeight(int);
1.3纸张大小(Z) SheetSetting #setPaperSize (PaperSize);
纸张大小的定义说明参见PaperSize类中的常量。
1.4起始页码(R) SheetSetting #setPageStrart(int);[默认状态]
2页面距
2.1上(T) SheetSetting # setTopMargin (double);
2.2下(B) SheetSetting # setBottomMargin (double);
2.3左(L) SheetSetting # setLeftMargin (double);
2.4右(R) SheetSetting # setRightMargin (double);
2.5页眉(A) SheetSetting #setHeaderMargin(double);
2.6页脚(F) SheetSetting #setFooterMargin(double);
2.7居中方式
2.7.1水平(Z) SheetSetting # setHorizontalCentre (boolean);
2.7.2垂直(V) SheetSetting #setVerticallyCenter(boolean);
3页眉/页脚
3.1页眉SheetSetting # setHeader(HeaderFooter);
说明:
对于HeaderFooter的设置,首先确定页眉的位置(左、中、右),通过HeaderFooter#getCentre()(或者getLeft()、getRight())方法获取HeaderFooter.Contents类,之后对这个类的属性进行操作。
下面简单介绍几个常用方法:
设置字号:Contents#setFontSize(int)
设置字体:Contents#setFontName(String)
设置内容:Contents# append(String),还有appendDate()当前日期等等,具体参考Contents类说明
3.2页脚SheetSetting # setFooter(HeaderFooter);
说明同上
4工作表
4.1打印区域
SheetSettings #setPrintArea(firstCol, firstRow, lastCol, lastRow)
4.2打印标题
SheetSettings#setPrintTitles (firstRow,lastRow,firstCol,lastCol);
SheetSettings#setPrintTitlesCol(firstCol,lastCol)
SheetSettings#setPrintTitlesRow(firstRow,lastRow)
另有一些其他的设置可在JXL API中找到,不多说了。
附件:通常在Excel中设置好打印,再将其读出来设置到当前页中来,附上代码:
在向Sheet页写数据之前:
1//获取原Sheet页的设置
2
3SheetSettings sheetSetting=sheet.getSettings();
4
5//将原sheet页的打印设置设置到当前Sheet页中
6
7sheet=new MjJxlExcelCopyPrintSetting().copySheetSettingToSheet(sheet, sheetSetting);
8
1下面是MjJxlExcelCopyPrintSetting的代码:
2
3import jxl.HeaderFooter;
4import jxl.Range;
5import jxl.SheetSettings;
6import jxl.format.PageOrientation;
7import jxl.format.PaperSize;
8import jxl.write.WritableSheet;
9
10/**
11 * 读取Jxl方法并设置
12
13 * @author 邱大为
14 * @version 1.0
15 */
16public class MjJxlExcelCopyPrintSetting {
17 /**
18 * 该方法将setting设置到sheet中
19 * @param sheet 需要设置的sheet
20 * @param setting 被设置的属性
21 * @return
22 */
23 public WritableSheet copySheetSettingToSheet(WritableSheet sheet,SheetSettings setting){
24// 设置原Sheet打印属性到新Sheet页
25 SheetSettings sheetSettings= sheet.getSettings();
26
27 sheetSettings.setAutomaticFormulaCalculation(setting.getAutomaticFormulaCalculation());
28 sheetSettings.setBottomMargin(setting.getBottomMargin());
29 sheetSettings.setCopies(setting.getCopies());
30 sheetSettings.setDefaultColumnWidth(setting.getDefaultColumnWidth());
31 sheetSettings.setDefaultRowHeight(setting.getDefaultRowHeight());
32 sheetSettings.setDisplayZeroValues(setting.getDisplayZeroValues());
33 sheetSettings.setFitHeight(setting.getFitHeight());
34 sheetSettings.setFitToPages(setting.getFitToPages());
35 sheetSettings.setFitWidth(setting.getFitWidth());
36
37 HeaderFooter footer=setting.getFooter();
38 if(footer!=null){
39 sheetSettings.setFooter(footer);
40 }
41 sheetSettings.setFooterMargin(setting.getFooterMargin());
42 HeaderFooter header=setting.getHeader();
43 if(header!=null){
44 sheetSettings.setHeader(header);
45 }
46 sheetSettings.setHeaderMargin(setting.getHeaderMargin());
47 sheetSettings.setHidden(setting.isHidden());
48 sheetSettings.setHorizontalCentre(setting.isHorizontalCentre());
49 sheetSettings.setHorizontalFreeze(setting.getHorizontalFreeze());
50 sheetSettings.setHorizontalPrintResolution(setting.getHorizontalPrintResolution());
51 sheetSettings.setLeftMargin(setting.getLeftMargin());
52 sheetSettings.setNormalMagnification(setting.getNormalMagnification());
53 PageOrientation pageOrientation=setting.getOrientation();
54 if(pageOrientation!=null){
55 sheetSettings.setOrientation(pageOrientation);
56 }
57 sheetSettings.setPageBreakPreviewMagnification(setting.getPageBreakPreviewMagnification());
58 sheetSettings.setPageBreakPreviewMode(setting.getPageBreakPreviewMode());
59 sheetSettings.setPageStart(setting.getPageStart());
60 PaperSize paperSize=setting.getPaperSize();
61 if(paperSize!=null){
62 sheetSettings.setPaperSize(setting.getPaperSize());
63 }
64
65 sheetSettings.setPassword(setting.getPassword());
66 sheetSettings.setPasswordHash(setting.getPasswordHash());
67 Range printArea=setting.getPrintArea();
68 if(printArea!=null){
69 sheetSettings.setPrintArea(printArea.getTopLeft()==null?0:printArea.getTopLeft().getColumn(),
70 printArea.getTopLeft()==null?0:printArea.getTopLeft().getRow(),
71 printArea.getBottomRight()==null?0:printArea.getBottomRight().getColumn(),
72 printArea.getBottomRight()==null?0:printArea.getBottomRight().getRow());
73 }
74
75 sheetSettings.setPrintGridLines(setting.getPrintGridLines());
76 sheetSettings.setPrintHeaders(setting.getPrintHeaders());
77
78 Range printTitlesCol=setting.getPrintTitlesCol();
79 if(printTitlesCol!=null){
80 sheetSettings.setPrintTitlesCol(printTitlesCol.getTopLeft()==null?0:printTitlesCol.getTopLeft().getColumn(),
81 printTitlesCol.getBottomRight()==null?0:printTitlesCol.getBottomRight().getColumn());
82 }
83 Range printTitlesRow=setting.getPrintTitlesRow();
84 if(printTitlesRow!=null){
85 sheetSettings.setPrintTitlesRow(printTitlesRow.getTopLeft()==null?0:printTitlesRow.getTopLeft().getRow(),
86 printTitlesRow.getBottomRight()==null?0:printTitlesRow.getBottomRight().getRow());
87 }
88
89 sheetSettings.setProtected(setting.isProtected());
90 sheetSettings.setRecalculateFormulasBeforeSave(setting.getRecalculateFormulasBeforeSave());
91 sheetSettings.setRightMargin(setting.getRightMargin());
92 sheetSettings.setScaleFactor(setting.getScaleFactor());
93 sheetSettings.setSelected(setting.isSelected());
94 sheetSettings.setShowGridLines(setting.getShowGridLines());
95 sheetSettings.setTopMargin(setting.getTopMargin());
96 sheetSettings.setVerticalCentre(setting.isVerticalCentre());
97 sheetSettings.setVerticalFreeze(setting.getVerticalFreeze());
98 sheetSettings.setVerticalPrintResolution(setting.getVerticalPrintResolution());
99 sheetSettings.setZoomFactor(setting.getZoomFactor());
100 return sheet;
101 }
102}
103
104
105
1.页面
1.1方向
1.1.1纵向(T)HSSFPrintSetup#setLandscape(false); [默认状态]
1.1.2横向(L)HSSFPrintSetup#setLandscape(true);
1.2缩放
1.2.1缩放比例(A)HSSFPrintSetup#setScale((short) 100);[默认状态]
1.2.2调整(F)
页宽 HSSFPrintSetup#setFitWidth((short) 1);
页高 HSSFPrintSetup#setFitHeight((short) 0);
1.3纸张大小(Z)HSSFPrintSetup#setPageSize(HSSFPrintSetup.LETTER_PAPERSIZE);
纸张大小的定义说明:
public static final short LETTER_PAPERSIZE = 1;
public static final short LEGAL_PAPERSIZE = 5;
public static final short EXECUTIVE_PAPERSIZE = 7;
public static final short A4_PAPERSIZE = 9;
public static final short A5_PAPERSIZE = 11;
public static final short ENVELOPE_10_PAPERSIZE = 20;
public static final short ENVELOPE_DL_PAPERSIZE = 27;
public static final short ENVELOPE_CS_PAPERSIZE = 28;
public static final short ENVELOPE_MONARCH_PAPERSIZE = 37;
1.4打印质量(Q)HSSFPrintSetup#setVResolution((short) 300)
1.5起始页码(R)HSSFPrintSetup#setPageStrart((short) 0);[默认状态]
2页面距
2.1上(T)HSSFSheet#setMargin(HSSFSheet.TopMargin,(short)0.6);
2.2下(B)HSSFSheet#setMargin(HSSFSheet.BottomMargin,(short)0.6);
2.3左(L)HSSFSheet#setMargin(HSSFSheet.LeftMargin,(short)0.6);
2.4右(R)HSSFSheet#setMargin(HSSFSheet.RightMargin,(short)0.2);
2.5页眉(A)HSSFPrintSetup#setHeaderMargin((double)0.2);
2.6页脚(F)HSSFPrintSetup#setFooterMargin((double)0.6);
2.7居中方式
2.7.1水平(Z)HSSFSheet#setHorizontallyCenter(false);
2.7.2垂直(V)HSSFSheet#setVerticallyCenter(false);
3页眉/页脚
3.1页眉HSSFHeader#setLeft(HSSFHeader.date();
说明:
首先获得HSSFHeader对象
确定页眉的显示位置(如,左边显示页眉HSSFHeader#setLeft(显示内容))
可使用HSSFHeader#setLeft,setCenter,setRight
3.2页脚HSSFFotter#setLeft(HSSFFotter.page()+”/”+HSSFFotter.numPages());
说明同3.1
首先获得HSSFFotter对象
确定页眉的显示位置(如,左边显示页眉