package org.apache.poi.hssf.usermodel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
public class TEST_COPY {
private static HSSFWorkbook readfile(String filename) throws IOException {
return new HSSFWorkbook(new FileInputStream(filename));
}
public static void test_read() throws Exception {
HSSFWorkbook wb = TEST_COPY.readfile("c://source_2.xls");
System.out.println("number of sheets: " + wb.getNumberOfSheets());
File file = new File("c:/workbook_2.xls");
if (!file.exists()) {
file.createNewFile();
}
file = null;
FileOutputStream fout = null;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("c:/workbook_2.xls"));
HSSFWorkbook target_wb = new HSSFWorkbook(fs);
int sheets = wb.getNumberOfSheets();
for (int i = 0; i < sheets; i++) {
copySheet(i, wb, target_wb);
fout = new FileOutputStream("c:/workbook_2.xls");
target_wb.write(fout);
fout.flush();
fout.close();
}
} finally {
if (fout != null)
fout.close();
}
}
public static void modifySheet() {
}
/**
* 复制sheet(从一个xls文件里的sheet复制到另一个xls文件里)
*
* @param source
* @param target
*/
private static void copySheet(int sheetindex, HSSFWorkbook source,HSSFWorkbook target) {
// 获取源sheet
HashMap<CellStyle, CellStyle> styles = new HashMap<CellStyle, CellStyle>();
HSSFSheet source_sheet = source.getSheetAt(sheetindex);
// 目标源sheet
HSSFSheet target_sheet = target.createSheet(source_sheet.getSheetName());
// setPrintSetup(source_sheet, target_sheet);
// 设置列宽
System.out.println("得到总行数=" + source_sheet.getLastRowNum());// 得到逻辑行数
int logic_rows = source_sheet.getLastRowNum() + 1;
int columns = source_sheet.getRow(0).getPhysicalNumberOfCells();
for (int i = 0; i < columns; i++) {
// System.out.println(i + ":" + source_sheet.getColumnWidth(i));
target_sheet.setColumnWidth(i, source_sheet.getColumnWidth(i));
// 判断隐藏列
if (source_sheet.isColumnHidden(i))
target_sheet.setColumnHidden(i, true);
}
int nType;
int nrow = 0;
Font t_font = null;
for (int ii = 0; ii < logic_rows; ii++) {
Row source_row = source_sheet.getRow(ii);
// System.out.println("得到总列数=" + source_row.getLastCellNum());
if (source_row == null) {
HSSFRow target_row = target_sheet.createRow(nrow);
// 设置行高
target_row.setHeight(source_row.getHeight());
nrow++;
continue;
}
int cells_logic = source_row.getLastCellNum();
HSSFRow target_row = target_sheet.createRow(nrow);// 创建一行
// 设置行高
target_row.setHeight(source_row.getHeight());
nrow++;
int j = 0;
for (int xx = 0; xx < cells_logic; xx++) {
Cell source_cell = source_row.getCell(xx);
if (source_cell == null) {
j++;
continue;
}
CellStyle target_all_cell_style = null;
Cell target_cell = target_row.createCell(j, source_cell.getCellType());
// CellStyle target_cell_style = target.createCellStyle();
nType = source_cell.getCellType();
// 设置Cell风格,创建一个新风格
// if (target_all_cell_style == null) {
// target_all_cell_style = target.createCellStyle();
// t_font = setCellStyle(styles, source, target, source_cell,
// target_cell, target_all_cell_style, t_font,
// target_sheet);
// } else {// 设置和比较风格是否相同
if (!compareStyle(styles, source, source_cell.getCellStyle(),target)) {// 表示不相等
target_all_cell_style = target.createCellStyle();
t_font = setCellStyle(styles, source, target, source_cell,target_cell, target_all_cell_style, t_font,
target_sheet);
} else
target_all_cell_style = styles.get(source_cell.getCellStyle());
target_cell.setCellStyle(target_all_cell_style);
switch (nType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
target_cell.setCellValue(source_cell.getBooleanCellValue());
// System.out.println("--------TYPE_BOOLEAN:" + j + " " + target_cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
RichTextString value = source_cell.getRichStringCellValue();
// value.applyFont(target_cell_style.getFontIndex());
target_cell.setCellValue(value);
// System.out.println("--------TYPE_STRING:" + j + " " + target_cell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
target_cell.setCellValue(source_cell.getNumericCellValue());
// System.out.println("--------TYPE_NUMERIC:" + j + " " +target_cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
try {
target_cell.setCellFormula(parseFormula(source_cell.getCellFormula()));
target_cell.setCellValue(parseFormula(source_cell.getCellFormula()));
// CellReference cell_r = new CellReference(source_cell.);
} catch (FormulaParseException e) {
throw e;
}
// System.out.print("--------TYPE_FORMULA:" + j + " " + target_cell.getCellFormula());
// System.out.println(" VALUE:" + source_cell.getNumericCellValue());
// evaluator.clearAllCachedResultValues();
break;
case HSSFCell.CELL_TYPE_ERROR:
target_cell.setCellErrorValue(source_cell.getErrorCellValue());
// System.out.println("--------TYPE_ERROR:" + j + " " + target_cell.getErrorCellValue());
case HSSFCell.CELL_TYPE_BLANK:
// target_cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
default:
break;
}
j++;
}
}
// 设置目标源打印设置
setPrintSetup(source_sheet, target_sheet);
System.out.println("target的字体数：" + target.getNumberOfFonts());
System.out.println("source的字体数：" + source.getNumberOfFonts());
// 处理合并单元格
mergedCell(source_sheet, target_sheet);
// 设置冻结单元
// target_sheet.createFreezePane(0, 3, 0, 3);
}
private static boolean compareStyle(HashMap<CellStyle, CellStyle> styles,
HSSFWorkbook source, CellStyle cellStyle, HSSFWorkbook target) {
for (CellStyle current : styles.keySet()) {
if (current.equals(cellStyle)) {
return true;
}
}
return false;
}
/**
* 合并单元格
*
* @param source_sheet
* @param target_sheet
*/
private static void mergedCell(HSSFSheet source_sheet,HSSFSheet target_sheet) {
System.out.println("合并单元格" + source_sheet.getNumMergedRegions());
int sum = source_sheet.getNumMergedRegions();
for (int i = 0; i < sum; i++) {
CellRangeAddress r = source_sheet.getMergedRegion(i);
// System.out.println("起始行/列:" + r.getFirstRow() + ":" + r.getFirstColumn());
// System.out.println("结束行/列:" + r.getLastRow() + ":" + r.getLastColumn());
target_sheet.addMergedRegion(new CellRangeAddress(r.getFirstRow(),r.getLastRow(), r.getFirstColumn(), r.getLastColumn()));
}
}
/**
* 设置单元格风格
*
* @param styles
*
* @param source
* @param target
* @param source_cell
* @param target_cell
* @param target_cell_style
* @param t_font
* @param target_sheet
* @return
*/
private static Font setCellStyle(HashMap<CellStyle, CellStyle> styles,
HSSFWorkbook source, HSSFWorkbook target, Cell source_cell,
Cell target_cell, CellStyle target_cell_style, Font t_font,
HSSFSheet target_sheet) {
CellStyle s_cell_style = source_cell.getCellStyle();
// 设置注释
setComment(source_cell, target_sheet, target_cell);
// target_cell_style.cloneStyleFrom(s_cell_style);这个从一个sheet复制到另一个sheet里有问题
target_cell_style.setAlignment(s_cell_style.getAlignment());
target_cell_style.setBorderBottom(s_cell_style.getBorderBottom());
target_cell_style.setBorderLeft(s_cell_style.getBorderLeft());
target_cell_style.setBorderRight(s_cell_style.getBorderRight());
target_cell_style.setBorderTop(s_cell_style.getBorderTop());
target_cell_style.setBottomBorderColor(s_cell_style.getBottomBorderColor());
//
target_cell_style.setDataFormat(s_cell_style.getDataFormat());
target_cell_style.setFillBackgroundColor(s_cell_style.getFillBackgroundColor());
target_cell_style.setFillForegroundColor(s_cell_style.getFillForegroundColor());
target_cell_style.setFillPattern(s_cell_style.getFillPattern());
// 获取源字体设置信息
Font s_font = source.getFontAt(s_cell_style.getFontIndex());
// 第一次创建
if (t_font == null) {
t_font = target.createFont();
// 设置字体相关属性
setFontStyle(s_font, t_font);
} else if (!checkFont(s_font, t_font)) {
// 判定是已存在该字体
Font find_font = target.findFont(s_font.getBoldweight(), s_font
.getColor(), s_font.getFontHeight(), s_font.getFontName(),
s_font.getItalic(), s_font.getStrikeout(), s_font
.getTypeOffset(), s_font.getUnderline());
if (find_font == null) {
t_font = target.createFont();
// 设置字体相关属性
setFontStyle(s_font, t_font);
} else
t_font = find_font;
}
target_cell_style.setFont(t_font);
//
target_cell_style.setWrapText(s_cell_style.getWrapText());
target_cell_style.setVerticalAlignment(s_cell_style.getVerticalAlignment());
target_cell_style.setTopBorderColor(s_cell_style.getTopBorderColor());
target_cell_style.setRotation(s_cell_style.getRotation());
target_cell_style.setRightBorderColor(s_cell_style.getRightBorderColor());
target_cell_style.setLocked(s_cell_style.getLocked());
target_cell_style.setLeftBorderColor(s_cell_style.getLeftBorderColor());
target_cell_style.setIndention(s_cell_style.getIndention());
target_cell_style.setHidden(s_cell_style.getHidden());
styles.put(s_cell_style, target_cell_style);
return t_font;
}
/**
* 设置注释
*
* @param source_cell
* @param target_sheet
* @param target_cell
*/
private static void setComment(Cell source_cell, HSSFSheet target_sheet,
Cell target_cell) {
HSSFComment s_comment = (HSSFComment) source_cell.getCellComment();
if (s_comment != null) {
HSSFPatriarch patr = target_sheet.createDrawingPatriarch();
HSSFClientAnchor s_clientAnchor = (HSSFClientAnchor) s_comment.getAnchor();
HSSFComment t_comment = new HSSFComment(s_comment.getNoteRecord(),
s_comment.getTextObjectRecord());
if (s_clientAnchor == null) {
t_comment = patr.createComment(new HSSFClientAnchor(100, 5, 20,100, (short) 6, 18, (short) 10, 10));
} else {
t_comment = patr.createComment(new HSSFClientAnchor(
s_clientAnchor.getDx1(), s_clientAnchor.getDy1(),
s_clientAnchor.getDx2(), s_clientAnchor.getDy2(),
s_clientAnchor.getCol1(), s_clientAnchor.getRow1(),
s_clientAnchor.getCol2(), s_clientAnchor.getRow2()));
}
// 设置注释内容
t_comment.setString(s_comment.getString());
t_comment.setAuthor(s_comment.getAuthor());
t_comment.setColumn(s_comment.getColumn());
t_comment.setFillColor(s_comment.getFillColor());
t_comment.setHorizontalAlignment(s_comment.getHorizontalAlignment());
t_comment.setLineStyle(s_comment.getLineStyle());
t_comment.setLineStyleColor(s_comment.getLineStyleColor());
t_comment.setLineWidth(s_comment.getLineWidth());
t_comment.setMarginBottom(s_comment.getMarginBottom());
t_comment.setMarginLeft(s_comment.getMarginLeft());
t_comment.setMarginRight(s_comment.getMarginRight());
t_comment.setMarginTop(s_comment.getMarginTop());
t_comment.setNoFill(s_comment.isNoFill());
t_comment.setRow(s_comment.getRow());
t_comment.setShapeType(s_comment.getShapeType());
t_comment.setVerticalAlignment(s_comment.getVerticalAlignment());
t_comment.setVisible(s_comment.isVisible());
target_cell.setCellComment(t_comment);
}
}
/**
* 比较两个字体是否相同，返回true表示相同，返回false表示不相同
*
* @param s_font
* @param t_font
* @param target
* @return
*/
private static boolean checkFont(Font s_font, Font t_font) {
return (s_font.getBoldweight() == t_font.getBoldweight())
&& (s_font.getColor() == t_font.getColor())
&& (s_font.getFontHeight() == t_font.getFontHeight())
&& (s_font.getFontName().equals(t_font.getFontName()))
&& (s_font.getItalic() == t_font.getItalic())
&& (s_font.getStrikeout() == t_font.getStrikeout())
&& (s_font.getTypeOffset() == t_font.getTypeOffset())
&& (s_font.getUnderline() == t_font.getUnderline());
}
/**
* 设置字体风格
*
* @param s_font
* @param t_font
*/
private static void setFontStyle(Font s_font, Font t_font) {
t_font.setBoldweight(s_font.getBoldweight());
// t_font.setCharSet((byte) s_font.getCharSet());
t_font.setCharSet(s_font.getCharSet());
t_font.setColor(s_font.getColor());
t_font.setFontHeight(s_font.getFontHeight());
t_font.setFontHeightInPoints(s_font.getFontHeightInPoints());
t_font.setFontName(s_font.getFontName());
t_font.setItalic(s_font.getItalic());
t_font.setStrikeout(s_font.getStrikeout());
t_font.setTypeOffset(s_font.getTypeOffset());
t_font.setUnderline(s_font.getUnderline());
}
/**
* 设置打印配置
*
* @param source_sheet
* @param target_sheet
*/
private static void setPrintSetup(HSSFSheet source_sheet,HSSFSheet target_sheet) {
HSSFPrintSetup s_printSetup = source_sheet.getPrintSetup();
HSSFPrintSetup t_printSetup = target_sheet.getPrintSetup();
t_printSetup.setCopies(s_printSetup.getCopies());
t_printSetup.setDraft(s_printSetup.getDraft());
t_printSetup.setFitHeight(s_printSetup.getFitHeight());
t_printSetup.setFitWidth(s_printSetup.getFitWidth());
t_printSetup.setFooterMargin(s_printSetup.getFooterMargin());
t_printSetup.setHeaderMargin(s_printSetup.getHeaderMargin());
t_printSetup.setHResolution(s_printSetup.getHResolution());
t_printSetup.setLandscape(s_printSetup.getLandscape());
t_printSetup.setLeftToRight(s_printSetup.getLeftToRight());
t_printSetup.setNoColor(s_printSetup.getNoColor());
t_printSetup.setNoOrientation(s_printSetup.getNoOrientation());
t_printSetup.setNotes(s_printSetup.getNotes());
t_printSetup.setOptions(s_printSetup.getOptions());
t_printSetup.setPageStart(s_printSetup.getPageStart());
t_printSetup.setPaperSize(s_printSetup.getPaperSize());
t_printSetup.setScale(s_printSetup.getScale());
t_printSetup.setUsePage(s_printSetup.getUsePage());
t_printSetup.setValidSettings(s_printSetup.getValidSettings());
t_printSetup.setVResolution(s_printSetup.getVResolution());
}
/**
* 公式设置
*
* @param pPOIFormula
* @return
*/
private static String parseFormula(String pPOIFormula) {
final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
StringBuffer result = null;
int index;
result = new StringBuffer();
index = pPOIFormula.indexOf(cstReplaceString);
if (index >= 0) {
result.append(pPOIFormula.substring(0, index));
result.append(pPOIFormula.substring(index+ cstReplaceString.length()));
} else {
result.append(pPOIFormula);
}
return result.toString();
}
public static void main(String[] args) throws Exception {
test_read();
}
}