1 先来看一下HSSFBOOK的一些基本操作,样式
public ActionResult excelPrint() {
HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet
sheet.createFreezePane(1, 3);// 冻结
// 设置列宽
sheet.setColumnWidth(0, 1000);
sheet.setColumnWidth(1, 3500);
sheet.setColumnWidth(2, 3500);
sheet.setColumnWidth(3, 6500);
sheet.setColumnWidth(4, 6500);
sheet.setColumnWidth(5, 6500);
sheet.setColumnWidth(6, 6500);
sheet.setColumnWidth(7, 2500);
// Sheet样式
HSSFCellStyle sheetStyle = workbook.createCellStyle();
// 背景色的设定
sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
// 前景色的设定
sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 填充模式
sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
// 设置列的样式
for (int i = 0; i <= 14; i++) {
sheet.setDefaultColumnStyle((short) i, sheetStyle);
}
// 设置字体
HSSFFont headfont = workbook.createFont();
headfont.setFontName("黑体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
// 另一个样式
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
headstyle.setWrapText(true);// 自动换行
// 另一个字体样式
HSSFFont columnHeadFont = workbook.createFont();
columnHeadFont.setFontName("宋体");
columnHeadFont.setFontHeightInPoints((short) 10);
columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 列头的样式
HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
columnHeadStyle.setFont(columnHeadFont);
columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
columnHeadStyle.setLocked(true);
columnHeadStyle.setWrapText(true);
columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
columnHeadStyle.setBorderLeft((short) 1);// 边框的大小
columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
columnHeadStyle.setBorderRight((short) 1);// 边框的大小
columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
// 设置单元格的背景颜色(单元格的样式会覆盖列或行的样式)
columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
// 普通单元格样式
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
style.setWrapText(true);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft((short) 1);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderRight((short) 1);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.
// 另一个样式
HSSFCellStyle centerstyle = workbook.createCellStyle();
centerstyle.setFont(font);
centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
centerstyle.setWrapText(true);
centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderLeft((short) 1);
centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderRight((short) 1);
centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.
try {
// 创建第一行
HSSFRow row0 = sheet.createRow(0);
// 设置行高
row0.setHeight((short) 900);
// 创建第一列
HSSFCell cell0 = row0.createCell(0);
cell0.setCellValue(new HSSFRichTextString("中非发展基金投资项目调度会工作落实情况对照表"));
cell0.setCellStyle(headstyle);
/**
* 合并单元格
* 第一个参数:第一个单元格的行数(从0开始)
* 第二个参数:第二个单元格的行数(从0开始)
* 第三个参数:第一个单元格的列数(从0开始)
* 第四个参数:第二个单元格的列数(从0开始)
*/
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7);
sheet.addMergedRegion(range);
// 创建第二行
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(new HSSFRichTextString("本次会议时间:2009年8月31日 前次会议时间:2009年8月24日"));
cell1.setCellStyle(centerstyle);
// 合并单元格
range = new CellRangeAddress(1, 2, 0, 7);
sheet.addMergedRegion(range);
// 第三行
HSSFRow row2 = sheet.createRow(3);
row2.setHeight((short) 750);
HSSFCell cell = row2.createCell(0);
cell.setCellValue(new HSSFRichTextString("责任者"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(1);
cell.setCellValue(new HSSFRichTextString("成熟度排序"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(2);
cell.setCellValue(new HSSFRichTextString("事项"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(3);
cell.setCellValue(new HSSFRichTextString("前次会议要求\n/新项目的项目概要"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(4);
cell.setCellValue(new HSSFRichTextString("上周工作进展"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(5);
cell.setCellValue(new HSSFRichTextString("本周工作计划"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(6);
cell.setCellValue(new HSSFRichTextString("问题和建议"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(7);
cell.setCellValue(new HSSFRichTextString("备 注"));
cell.setCellStyle(columnHeadStyle);
// 访问数据库,得到数据集
List<DeitelVO> deitelVOList = getEntityManager().queryDeitelVOList();
int m = 4;
int k = 4;
for (int i = 0; i < deitelVOList.size(); i++) {
DeitelVO vo = deitelVOList.get(i);
String dname = vo.getDname();
List<Workinfo> workList = vo.getWorkInfoList();
HSSFRow row = sheet.createRow(m);
cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString(dname));
cell.setCellStyle(centerstyle);
// 合并单元格
range = new CellRangeAddress(m, m + workList.size() - 1, 0, 0);
sheet.addMergedRegion(range);
m = m + workList.size();
for (int j = 0; j < workList.size(); j++) {
Workinfo w = workList.get(j);
// 遍历数据集创建Excel的行
row = sheet.getRow(k + j);
if (null == row) {
row = sheet.createRow(k + j);
}
cell = row.createCell(1);
cell.setCellValue(w.getWnumber());
cell.setCellStyle(centerstyle);
cell = row.createCell(2);
cell.setCellValue(new HSSFRichTextString(w.getWitem()));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue(new HSSFRichTextString(w.getWmeting()));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue(new HSSFRichTextString(w.getWbweek()));
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue(new HSSFRichTextString(w.getWtweek()));
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue(new HSSFRichTextString(w.getWproblem()));
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue(new HSSFRichTextString(w.getWremark()));
cell.setCellStyle(style);
}
k = k + workList.size();
}
// 列尾
int footRownumber = sheet.getLastRowNum();
HSSFRow footRow = sheet.createRow(footRownumber + 1);
HSSFCell footRowcell = footRow.createCell(0);
footRowcell.setCellValue(new HSSFRichTextString(" 审 定:XXX 审 核:XXX 汇 总:XX"));
footRowcell.setCellStyle(centerstyle);
range = new CellRangeAddress(footRownumber + 1, footRownumber + 1, 0, 7);
sheet.addMergedRegion(range);
HttpServletResponse response = getResponse();
HttpServletRequest request = getRequest();
String filename = "未命名.xls";//设置下载时客户端Excel的名称
// 请见:http://zmx.iteye.com/blog/622529
filename = Util.encodeFilename(filename, request);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
2 POI的一些方法
一、 POI简介
二、 HSSF概况
三、 POI EXCEL文档结构类
四、 EXCEL常用操作方法
1、 得到Excel常用对象
[c-sharp] view plaincopyprint?
- POIFSFileSystem
fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); - //得到Excel工作簿对象
- HSSFWorkbook
wb = new HSSFWorkbook(fs); - //得到Excel工作表对象
- HSSFSheet
sheet = wb.getSheetAt(0); - //得到Excel工作表的行
- HSSFRow
row = sheet.getRow(i); - //得到Excel工作表指定行的单元格
- HSSFCell
cell = row.getCell((short) j); - cellStyle
= cell.getCellStyle();//得到单元格样式
2、建立Excel常用对象
[c-sharp] view plaincopyprint?
- HSSFWorkbook
wb = new HSSFWorkbook();//创建Excel工作簿对象 - HSSFSheet
sheet = wb.createSheet("new sheet");//创建Excel工作表对象 - HSSFRow
row = sheet.createRow((short)0); //创建Excel工作表的行 - cellStyle
= wb.createCellStyle();//创建单元格样式 - row.createCell((short)0).setCellStyle(cellStyle);
//创建Excel工作表指定行的单元格 - row.createCell((short)0).setCellValue(1);
//设置Excel工作表的值
3、设置sheet名称和单元格内容
[c-sharp] view plaincopyprint?
- wb.setSheetName(1,
"第一张工作表",HSSFCell.ENCODING_UTF_16); - cell.setEncoding((short)
1); - cell.setCellValue("单元格内容");
4、取得sheet的数目
[c-sharp] view plaincopyprint?
- wb.getNumberOfSheets()
5、 根据index取得sheet对象
[c-sharp] view plaincopyprint?
- HSSFSheet
sheet = wb.getSheetAt(0);
6、取得有效的行数
[c-sharp] view plaincopyprint?
- int
rowcount = sheet.getLastRowNum();
7、取得一行的有效单元格个数
[c-sharp] view plaincopyprint?
- row.getLastCellNum();
8、单元格值类型读写
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//设置单元格为STRING类型 - cell.getNumericCellValue();//读取为数值类型的单元格内容
9、设置列宽、行高
- sheet.setColumnWidth((short)column,(short)width);
- row.setHeight((short)height);
10、添加区域,合并单元格
- Region
region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo - ,(short)columnTo);//合并从第rowFrom行columnFrom列
- sheet.addMergedRegion(region);//
到rowTo行columnTo的区域 - //得到所有区域
- sheet.getNumMergedRegions()
11、保存Excel文件
- FileOutputStream
fileOut = new FileOutputStream(path); - wb.write(fileOut);
12、根据单元格不同属性返回字符串数值
- public
String getCellStringValue(HSSFCell cell) { -
String cellValue = ""; -
switch (cell.getCellType()) { -
case HSSFCell.CELL_TYPE_STRING://字符串类型 -
cellValue = cell.getStringCellValue(); -
if(cellValue.trim().equals("")||cellValue.trim().length()<=0) -
cellValue=" "; -
break; -
case HSSFCell.CELL_TYPE_NUMERIC: //数值类型 -
cellValue = String.valueOf(cell.getNumericCellValue()); -
break; -
case HSSFCell.CELL_TYPE_FORMULA: //公式 -
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); -
cellValue = String.valueOf(cell.getNumericCellValue()); -
break; -
case HSSFCell.CELL_TYPE_BLANK: -
cellValue=" "; -
break; -
case HSSFCell.CELL_TYPE_BOOLEAN: -
break; -
case HSSFCell.CELL_TYPE_ERROR: -
break; -
default: -
break; -
} -
return cellValue; -
}
13、常用单元格边框格式
- HSSFCellStyle
style = wb.createCellStyle(); - style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
- style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
14、设置字体和内容位置
- HSSFFont
f = wb.createFont(); - f.setFontHeightInPoints((short)
11);//字号 - f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
- style.setFont(f);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
- style.setRotation(short
rotation);//单元格内容的旋转的角度 - HSSFDataFormat
df = wb.createDataFormat(); - style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
- cell.setCellFormula(string);//给单元格设公式
- style.setRotation(short
rotation);//单元格内容的旋转的角度
15、插入图片
- //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
-
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); -
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); -
ImageIO.write(bufferImg,"jpg",byteArrayOut); - //读进一个excel模版
- FileInputStream
fos = new FileInputStream(filePathName+"/stencil.xlt"); - fs
= new POIFSFileSystem(fos); - //创建一个工作薄
- HSSFWorkbook
wb = new HSSFWorkbook(fs); - HSSFSheet
sheet = wb.getSheetAt(0); - HSSFPatriarch
patriarch = sheet.createDrawingPatriarch(); - HSSFClientAnchor
anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); - patriarch.createPicture(anchor
, wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、调整工作表位置
- HSSFWorkbook
wb = new HSSFWorkbook(); - HSSFSheet
sheet = wb.createSheet("format sheet"); - HSSFPrintSetup
ps = sheet.getPrintSetup(); - sheet.setAutobreaks(true);
- ps.setFitHeight((short)1);
- ps.setFitWidth((short)1);
17、设置打印区域
- HSSFSheet
sheet = wb.createSheet("Sheet1"); - wb.setPrintArea(0,
"$A$1:$C$2");
18、标注脚注
- HSSFSheet
sheet = wb.createSheet("format sheet"); - HSSFFooter
footer = sheet.getFooter() - footer.setRight(
"Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
19、在工作单中清空行数据,调整行位置
- HSSFWorkbook
wb = new HSSFWorkbook(); - HSSFSheet
sheet = wb.createSheet("row sheet"); - //
Create various cells and rows for spreadsheet. - //
Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) - sheet.shiftRows(5,
10, -5);
20、选中指定的工作表
- HSSFSheet
sheet = wb.createSheet("row sheet"); - heet.setSelected(true);
21、工作表的放大缩小
- HSSFSheet
sheet1 = wb.createSheet("new sheet"); - sheet1.setZoom(1,2);
// 50 percent magnification
22、头注和脚注
- HSSFSheet
sheet = wb.createSheet("new sheet"); - HSSFHeader
header = sheet.getHeader(); - header.setCenter("Center
Header"); - header.setLeft("Left
Header"); - header.setRight(HSSFHeader.font("Stencil-Normal",
"Italic") + - HSSFHeader.fontSize((short)
16) + "Right w/ Stencil-Normal Italic font and size 16");
23、自定义颜色
- HSSFCellStyle
style = wb.createCellStyle(); - style.setFillForegroundColor(HSSFColor.LIME.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- HSSFFont
font = wb.createFont(); - font.setColor(HSSFColor.RED.index);
- style.setFont(font);
- cell.setCellStyle(style);
24、填充和颜色设置
- HSSFCellStyle
style = wb.createCellStyle(); - style.setFillBackgroundColor(HSSFColor.AQUA.index);
- style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
- HSSFCell
cell = row.createCell((short) 1); - cell.setCellValue("X");
- style
= wb.createCellStyle(); - style.setFillForegroundColor(HSSFColor.ORANGE.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- cell.setCellStyle(style);
25、强行刷新单元格公式
- HSSFFormulaEvaluator
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb); - private
static void updateFormula(Workbook wb,Sheet s,int row){ -
Row r=s.getRow(row); -
Cell c=null; -
FormulaEcaluator eval=null; -
if(wb instanceof HSSFWorkbook) -
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb); -
else if(wb instanceof XSSFWorkbook) -
eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb); -
for(int i=r.getFirstCellNum();i -
c=r.getCell(i); -
if(c.getCellType()==Cell.CELL_TYPE_FORMULA) -
eval.evaluateFormulaCell(c); -
} -
}
说明:FormulaEvaluator提供了evaluateFormulaCell(Cell cell)方法,计算公式保存结果,但不改变公式。而evaluateInCell(Cell cell) 方法是计算公式,并将原公式替换为计算结果,也就是说该单元格的类型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了静态方法evaluateAllFormu
laCells(HSSFWorkbook wb) ,计算一个Excel文件的所有公式,用起来很方便。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------poi方法总结
-.设置不显示excel网格线
2.设置excel单元格中的内容换行
topTile.append("/r/n" +"cellContent");
3.单元格的合并
4.设置页眉和页脚的页数
5.使得一个Sheet适合一页
6.设置放大属性(Zoom被明确为一个分数,例如下面的75%使用3作为分子,4作为分母)
7.设置打印
8.反复的行和列(设置打印标题)
9.调整单元格宽度
sheet.setAutobreaks(true);
sheet.setColumnWidth((short)i,colsWidth[i]); //设定单元格长度
----------------------------------------------------------------------------------------------------------------------------------------------------------
3表行的一些基本复制操作
public class POIUtils {
// /**
// * 把一个excel中的cellstyletable复制到另一个excel,这里会报错,不能用这种方法,不明白呀?????
// * @param fromBook
// * @param toBook
// */
// public static void copyBookCellStyle(HSSFWorkbook fromBook,HSSFWorkbook toBook){
// for(short i=0;i<fromBook.getNumCellStyles();i++){
// HSSFCellStyle fromStyle=fromBook.getCellStyleAt(i);
// HSSFCellStyle toStyle=toBook.getCellStyleAt(i);
// if(toStyle==null){
// toStyle=toBook.createCellStyle();
// }
// copyCellStyle(fromStyle,toStyle);
// }
// }
/**
* 复制一个单元格样式到目的单元格样式
* @param fromStyle
* @param toStyle
*/
public static void copyCellStyle(HSSFCellStyle fromStyle,
HSSFCellStyle toStyle) {
toStyle.setAlignment(fromStyle.getAlignment());
//边框和边框颜色
toStyle.setBorderBottom(fromStyle.getBorderBottom());
toStyle.setBorderLeft(fromStyle.getBorderLeft());
toStyle.setBorderRight(fromStyle.getBorderRight());
toStyle.setBorderTop(fromStyle.getBorderTop());
toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
//背景和前景
toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
toStyle.setDataFormat(fromStyle.getDataFormat());
toStyle.setFillPattern(fromStyle.getFillPattern());
// toStyle.setFont(fromStyle.getFont(null));
toStyle.setHidden(fromStyle.getHidden());
toStyle.setIndention(fromStyle.getIndention());//首行缩进
toStyle.setLocked(fromStyle.getLocked());
toStyle.setRotation(fromStyle.getRotation());//旋转
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
toStyle.setWrapText(fromStyle.getWrapText());
}
/**
* Sheet复制
* @param fromSheet
* @param toSheet
* @param copyValueFlag
*/
public static void copySheet(HSSFWorkbook wb,HSSFSheet fromSheet, HSSFSheet toSheet,
boolean copyValueFlag) {
//合并区域处理
mergerRegion(fromSheet, toSheet);
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
HSSFRow tmpRow = (HSSFRow) rowIt.next();
HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());
//行复制
copyRow(wb,tmpRow,newRow,copyValueFlag);
}
}
/**
* 行复制功能
* @param fromRow
* @param toRow
*/
public static void copyRow(HSSFWorkbook wb,HSSFRow fromRow,HSSFRow toRow,boolean copyValueFlag){
for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
HSSFCell tmpCell = (HSSFCell) cellIt.next();
HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
copyCell(wb,tmpCell, newCell, copyValueFlag);
}
}
/**
* 复制原有sheet的合并单元格到新创建的sheet
*
* @param sheetCreat 新创建sheet
* @param sheet 原有的sheet
*/
public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
int sheetMergerCount = fromSheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
Region mergedRegionAt = fromSheet.getMergedRegionAt(i);
toSheet.addMergedRegion(mergedRegionAt);
}
}
/**
* 复制单元格
*
* @param srcCell
* @param distCell
* @param copyValueFlag
* true则连同cell的内容一起复制
*/
public static void copyCell(HSSFWorkbook wb,HSSFCell srcCell, HSSFCell distCell,
boolean copyValueFlag) {
HSSFCellStyle newstyle=wb.createCellStyle();
copyCellStyle(srcCell.getCellStyle(), newstyle);
distCell.setEncoding(srcCell.getEncoding());
//样式
distCell.setCellStyle(newstyle);
//评论
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
// 不同数据类型处理
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (copyValueFlag) {
if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else { // nothing29
}
}
}
}
1 先来看一下HSSFBOOK的一些基本操作,样式
public ActionResult excelPrint() {
HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet
sheet.createFreezePane(1, 3);// 冻结
// 设置列宽
sheet.setColumnWidth(0, 1000);
sheet.setColumnWidth(1, 3500);
sheet.setColumnWidth(2, 3500);
sheet.setColumnWidth(3, 6500);
sheet.setColumnWidth(4, 6500);
sheet.setColumnWidth(5, 6500);
sheet.setColumnWidth(6, 6500);
sheet.setColumnWidth(7, 2500);
// Sheet样式
HSSFCellStyle sheetStyle = workbook.createCellStyle();
// 背景色的设定
sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
// 前景色的设定
sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 填充模式
sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
// 设置列的样式
for (int i = 0; i <= 14; i++) {
sheet.setDefaultColumnStyle((short) i, sheetStyle);
}
// 设置字体
HSSFFont headfont = workbook.createFont();
headfont.setFontName("黑体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
// 另一个样式
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
headstyle.setWrapText(true);// 自动换行
// 另一个字体样式
HSSFFont columnHeadFont = workbook.createFont();
columnHeadFont.setFontName("宋体");
columnHeadFont.setFontHeightInPoints((short) 10);
columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 列头的样式
HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
columnHeadStyle.setFont(columnHeadFont);
columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
columnHeadStyle.setLocked(true);
columnHeadStyle.setWrapText(true);
columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
columnHeadStyle.setBorderLeft((short) 1);// 边框的大小
columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
columnHeadStyle.setBorderRight((short) 1);// 边框的大小
columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
// 设置单元格的背景颜色(单元格的样式会覆盖列或行的样式)
columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
// 普通单元格样式
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
style.setWrapText(true);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft((short) 1);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderRight((short) 1);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.
// 另一个样式
HSSFCellStyle centerstyle = workbook.createCellStyle();
centerstyle.setFont(font);
centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
centerstyle.setWrapText(true);
centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderLeft((short) 1);
centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderRight((short) 1);
centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.
try {
// 创建第一行
HSSFRow row0 = sheet.createRow(0);
// 设置行高
row0.setHeight((short) 900);
// 创建第一列
HSSFCell cell0 = row0.createCell(0);
cell0.setCellValue(new HSSFRichTextString("中非发展基金投资项目调度会工作落实情况对照表"));
cell0.setCellStyle(headstyle);
/**
* 合并单元格
* 第一个参数:第一个单元格的行数(从0开始)
* 第二个参数:第二个单元格的行数(从0开始)
* 第三个参数:第一个单元格的列数(从0开始)
* 第四个参数:第二个单元格的列数(从0开始)
*/
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7);
sheet.addMergedRegion(range);
// 创建第二行
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(new HSSFRichTextString("本次会议时间:2009年8月31日 前次会议时间:2009年8月24日"));
cell1.setCellStyle(centerstyle);
// 合并单元格
range = new CellRangeAddress(1, 2, 0, 7);
sheet.addMergedRegion(range);
// 第三行
HSSFRow row2 = sheet.createRow(3);
row2.setHeight((short) 750);
HSSFCell cell = row2.createCell(0);
cell.setCellValue(new HSSFRichTextString("责任者"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(1);
cell.setCellValue(new HSSFRichTextString("成熟度排序"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(2);
cell.setCellValue(new HSSFRichTextString("事项"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(3);
cell.setCellValue(new HSSFRichTextString("前次会议要求\n/新项目的项目概要"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(4);
cell.setCellValue(new HSSFRichTextString("上周工作进展"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(5);
cell.setCellValue(new HSSFRichTextString("本周工作计划"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(6);
cell.setCellValue(new HSSFRichTextString("问题和建议"));
cell.setCellStyle(columnHeadStyle);
cell = row2.createCell(7);
cell.setCellValue(new HSSFRichTextString("备 注"));
cell.setCellStyle(columnHeadStyle);
// 访问数据库,得到数据集
List<DeitelVO> deitelVOList = getEntityManager().queryDeitelVOList();
int m = 4;
int k = 4;
for (int i = 0; i < deitelVOList.size(); i++) {
DeitelVO vo = deitelVOList.get(i);
String dname = vo.getDname();
List<Workinfo> workList = vo.getWorkInfoList();
HSSFRow row = sheet.createRow(m);
cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString(dname));
cell.setCellStyle(centerstyle);
// 合并单元格
range = new CellRangeAddress(m, m + workList.size() - 1, 0, 0);
sheet.addMergedRegion(range);
m = m + workList.size();
for (int j = 0; j < workList.size(); j++) {
Workinfo w = workList.get(j);
// 遍历数据集创建Excel的行
row = sheet.getRow(k + j);
if (null == row) {
row = sheet.createRow(k + j);
}
cell = row.createCell(1);
cell.setCellValue(w.getWnumber());
cell.setCellStyle(centerstyle);
cell = row.createCell(2);
cell.setCellValue(new HSSFRichTextString(w.getWitem()));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue(new HSSFRichTextString(w.getWmeting()));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue(new HSSFRichTextString(w.getWbweek()));
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue(new HSSFRichTextString(w.getWtweek()));
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue(new HSSFRichTextString(w.getWproblem()));
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue(new HSSFRichTextString(w.getWremark()));
cell.setCellStyle(style);
}
k = k + workList.size();
}
// 列尾
int footRownumber = sheet.getLastRowNum();
HSSFRow footRow = sheet.createRow(footRownumber + 1);
HSSFCell footRowcell = footRow.createCell(0);
footRowcell.setCellValue(new HSSFRichTextString(" 审 定:XXX 审 核:XXX 汇 总:XX"));
footRowcell.setCellStyle(centerstyle);
range = new CellRangeAddress(footRownumber + 1, footRownumber + 1, 0, 7);
sheet.addMergedRegion(range);
HttpServletResponse response = getResponse();
HttpServletRequest request = getRequest();
String filename = "未命名.xls";//设置下载时客户端Excel的名称
// 请见:http://zmx.iteye.com/blog/622529
filename = Util.encodeFilename(filename, request);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
2 POI的一些方法
一、 POI简介
二、 HSSF概况
三、 POI EXCEL文档结构类
四、 EXCEL常用操作方法
1、 得到Excel常用对象
[c-sharp] view plaincopyprint?
- POIFSFileSystem
fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); - //得到Excel工作簿对象
- HSSFWorkbook
wb = new HSSFWorkbook(fs); - //得到Excel工作表对象
- HSSFSheet
sheet = wb.getSheetAt(0); - //得到Excel工作表的行
- HSSFRow
row = sheet.getRow(i); - //得到Excel工作表指定行的单元格
- HSSFCell
cell = row.getCell((short) j); - cellStyle
= cell.getCellStyle();//得到单元格样式
2、建立Excel常用对象
[c-sharp] view plaincopyprint?
- HSSFWorkbook
wb = new HSSFWorkbook();//创建Excel工作簿对象 - HSSFSheet
sheet = wb.createSheet("new sheet");//创建Excel工作表对象 - HSSFRow
row = sheet.createRow((short)0); //创建Excel工作表的行 - cellStyle
= wb.createCellStyle();//创建单元格样式 - row.createCell((short)0).setCellStyle(cellStyle);
//创建Excel工作表指定行的单元格 - row.createCell((short)0).setCellValue(1);
//设置Excel工作表的值
3、设置sheet名称和单元格内容
[c-sharp] view plaincopyprint?
- wb.setSheetName(1,
"第一张工作表",HSSFCell.ENCODING_UTF_16); - cell.setEncoding((short)
1); - cell.setCellValue("单元格内容");
4、取得sheet的数目
[c-sharp] view plaincopyprint?
- wb.getNumberOfSheets()
5、 根据index取得sheet对象
[c-sharp] view plaincopyprint?
- HSSFSheet
sheet = wb.getSheetAt(0);
6、取得有效的行数
[c-sharp] view plaincopyprint?
- int
rowcount = sheet.getLastRowNum();
7、取得一行的有效单元格个数
[c-sharp] view plaincopyprint?
- row.getLastCellNum();
8、单元格值类型读写
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//设置单元格为STRING类型 - cell.getNumericCellValue();//读取为数值类型的单元格内容
9、设置列宽、行高
- sheet.setColumnWidth((short)column,(short)width);
- row.setHeight((short)height);
10、添加区域,合并单元格
- Region
region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo - ,(short)columnTo);//合并从第rowFrom行columnFrom列
- sheet.addMergedRegion(region);//
到rowTo行columnTo的区域 - //得到所有区域
- sheet.getNumMergedRegions()
11、保存Excel文件
- FileOutputStream
fileOut = new FileOutputStream(path); - wb.write(fileOut);
12、根据单元格不同属性返回字符串数值
- public
String getCellStringValue(HSSFCell cell) { -
String cellValue = ""; -
switch (cell.getCellType()) { -
case HSSFCell.CELL_TYPE_STRING://字符串类型 -
cellValue = cell.getStringCellValue(); -
if(cellValue.trim().equals("")||cellValue.trim().length()<=0) -
cellValue=" "; -
break; -
case HSSFCell.CELL_TYPE_NUMERIC: //数值类型 -
cellValue = String.valueOf(cell.getNumericCellValue()); -
break; -
case HSSFCell.CELL_TYPE_FORMULA: //公式 -
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); -
cellValue = String.valueOf(cell.getNumericCellValue()); -
break; -
case HSSFCell.CELL_TYPE_BLANK: -
cellValue=" "; -
break; -
case HSSFCell.CELL_TYPE_BOOLEAN: -
break; -
case HSSFCell.CELL_TYPE_ERROR: -
break; -
default: -
break; -
} -
return cellValue; -
}
13、常用单元格边框格式
- HSSFCellStyle
style = wb.createCellStyle(); - style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
- style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
14、设置字体和内容位置
- HSSFFont
f = wb.createFont(); - f.setFontHeightInPoints((short)
11);//字号 - f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
- style.setFont(f);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
- style.setRotation(short
rotation);//单元格内容的旋转的角度 - HSSFDataFormat
df = wb.createDataFormat(); - style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
- cell.setCellFormula(string);//给单元格设公式
- style.setRotation(short
rotation);//单元格内容的旋转的角度
15、插入图片
- //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
-
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); -
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); -
ImageIO.write(bufferImg,"jpg",byteArrayOut); - //读进一个excel模版
- FileInputStream
fos = new FileInputStream(filePathName+"/stencil.xlt"); - fs
= new POIFSFileSystem(fos); - //创建一个工作薄
- HSSFWorkbook
wb = new HSSFWorkbook(fs); - HSSFSheet
sheet = wb.getSheetAt(0); - HSSFPatriarch
patriarch = sheet.createDrawingPatriarch(); - HSSFClientAnchor
anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); - patriarch.createPicture(anchor
, wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、调整工作表位置
- HSSFWorkbook
wb = new HSSFWorkbook(); - HSSFSheet
sheet = wb.createSheet("format sheet"); - HSSFPrintSetup
ps = sheet.getPrintSetup(); - sheet.setAutobreaks(true);
- ps.setFitHeight((short)1);
- ps.setFitWidth((short)1);
17、设置打印区域
- HSSFSheet
sheet = wb.createSheet("Sheet1"); - wb.setPrintArea(0,
"$A$1:$C$2");
18、标注脚注
- HSSFSheet
sheet = wb.createSheet("format sheet"); - HSSFFooter
footer = sheet.getFooter() - footer.setRight(
"Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
19、在工作单中清空行数据,调整行位置
- HSSFWorkbook
wb = new HSSFWorkbook(); - HSSFSheet
sheet = wb.createSheet("row sheet"); - //
Create various cells and rows for spreadsheet. - //
Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) - sheet.shiftRows(5,
10, -5);
20、选中指定的工作表
- HSSFSheet
sheet = wb.createSheet("row sheet"); - heet.setSelected(true);
21、工作表的放大缩小
- HSSFSheet
sheet1 = wb.createSheet("new sheet"); - sheet1.setZoom(1,2);
// 50 percent magnification
22、头注和脚注
- HSSFSheet
sheet = wb.createSheet("new sheet"); - HSSFHeader
header = sheet.getHeader(); - header.setCenter("Center
Header"); - header.setLeft("Left
Header"); - header.setRight(HSSFHeader.font("Stencil-Normal",
"Italic") + - HSSFHeader.fontSize((short)
16) + "Right w/ Stencil-Normal Italic font and size 16");
23、自定义颜色
- HSSFCellStyle
style = wb.createCellStyle(); - style.setFillForegroundColor(HSSFColor.LIME.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- HSSFFont
font = wb.createFont(); - font.setColor(HSSFColor.RED.index);
- style.setFont(font);
- cell.setCellStyle(style);
24、填充和颜色设置
- HSSFCellStyle
style = wb.createCellStyle(); - style.setFillBackgroundColor(HSSFColor.AQUA.index);
- style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
- HSSFCell
cell = row.createCell((short) 1); - cell.setCellValue("X");
- style
= wb.createCellStyle(); - style.setFillForegroundColor(HSSFColor.ORANGE.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- cell.setCellStyle(style);
25、强行刷新单元格公式
- HSSFFormulaEvaluator
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb); - private
static void updateFormula(Workbook wb,Sheet s,int row){ -
Row r=s.getRow(row); -
Cell c=null; -
FormulaEcaluator eval=null; -
if(wb instanceof HSSFWorkbook) -
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb); -
else if(wb instanceof XSSFWorkbook) -
eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb); -
for(int i=r.getFirstCellNum();i -
c=r.getCell(i); -
if(c.getCellType()==Cell.CELL_TYPE_FORMULA) -
eval.evaluateFormulaCell(c); -
} -
}
说明:FormulaEvaluator提供了evaluateFormulaCell(Cell cell)方法,计算公式保存结果,但不改变公式。而evaluateInCell(Cell cell) 方法是计算公式,并将原公式替换为计算结果,也就是说该单元格的类型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了静态方法evaluateAllFormu
laCells(HSSFWorkbook wb) ,计算一个Excel文件的所有公式,用起来很方便。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------poi方法总结
-.设置不显示excel网格线
2.设置excel单元格中的内容换行
topTile.append("/r/n" +"cellContent");
3.单元格的合并
4.设置页眉和页脚的页数
5.使得一个Sheet适合一页
6.设置放大属性(Zoom被明确为一个分数,例如下面的75%使用3作为分子,4作为分母)
7.设置打印
8.反复的行和列(设置打印标题)
9.调整单元格宽度
sheet.setAutobreaks(true);
sheet.setColumnWidth((short)i,colsWidth[i]); //设定单元格长度
----------------------------------------------------------------------------------------------------------------------------------------------------------
3表行的一些基本复制操作
public class POIUtils {
// /**
// * 把一个excel中的cellstyletable复制到另一个excel,这里会报错,不能用这种方法,不明白呀?????
// * @param fromBook
// * @param toBook
// */
// public static void copyBookCellStyle(HSSFWorkbook fromBook,HSSFWorkbook toBook){
// for(short i=0;i<fromBook.getNumCellStyles();i++){
// HSSFCellStyle fromStyle=fromBook.getCellStyleAt(i);
// HSSFCellStyle toStyle=toBook.getCellStyleAt(i);
// if(toStyle==null){
// toStyle=toBook.createCellStyle();
// }
// copyCellStyle(fromStyle,toStyle);
// }
// }
/**
* 复制一个单元格样式到目的单元格样式
* @param fromStyle
* @param toStyle
*/
public static void copyCellStyle(HSSFCellStyle fromStyle,
HSSFCellStyle toStyle) {
toStyle.setAlignment(fromStyle.getAlignment());
//边框和边框颜色
toStyle.setBorderBottom(fromStyle.getBorderBottom());
toStyle.setBorderLeft(fromStyle.getBorderLeft());
toStyle.setBorderRight(fromStyle.getBorderRight());
toStyle.setBorderTop(fromStyle.getBorderTop());
toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
//背景和前景
toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
toStyle.setDataFormat(fromStyle.getDataFormat());
toStyle.setFillPattern(fromStyle.getFillPattern());
// toStyle.setFont(fromStyle.getFont(null));
toStyle.setHidden(fromStyle.getHidden());
toStyle.setIndention(fromStyle.getIndention());//首行缩进
toStyle.setLocked(fromStyle.getLocked());
toStyle.setRotation(fromStyle.getRotation());//旋转
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
toStyle.setWrapText(fromStyle.getWrapText());
}
/**
* Sheet复制
* @param fromSheet
* @param toSheet
* @param copyValueFlag
*/
public static void copySheet(HSSFWorkbook wb,HSSFSheet fromSheet, HSSFSheet toSheet,
boolean copyValueFlag) {
//合并区域处理
mergerRegion(fromSheet, toSheet);
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
HSSFRow tmpRow = (HSSFRow) rowIt.next();
HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());
//行复制
copyRow(wb,tmpRow,newRow,copyValueFlag);
}
}
/**
* 行复制功能
* @param fromRow
* @param toRow
*/
public static void copyRow(HSSFWorkbook wb,HSSFRow fromRow,HSSFRow toRow,boolean copyValueFlag){
for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
HSSFCell tmpCell = (HSSFCell) cellIt.next();
HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
copyCell(wb,tmpCell, newCell, copyValueFlag);
}
}
/**
* 复制原有sheet的合并单元格到新创建的sheet
*
* @param sheetCreat 新创建sheet
* @param sheet 原有的sheet
*/
public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
int sheetMergerCount = fromSheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
Region mergedRegionAt = fromSheet.getMergedRegionAt(i);
toSheet.addMergedRegion(mergedRegionAt);
}
}
/**
* 复制单元格
*
* @param srcCell
* @param distCell
* @param copyValueFlag
* true则连同cell的内容一起复制
*/
public static void copyCell(HSSFWorkbook wb,HSSFCell srcCell, HSSFCell distCell,
boolean copyValueFlag) {
HSSFCellStyle newstyle=wb.createCellStyle();
copyCellStyle(srcCell.getCellStyle(), newstyle);
distCell.setEncoding(srcCell.getEncoding());
//样式
distCell.setCellStyle(newstyle);
//评论
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
// 不同数据类型处理
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (copyValueFlag) {
if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else { // nothing29
}
}
}
}