upload Excel file to spring mvc
@RequestMapping(value = "/importExcel.do", method = RequestMethod.POST)
@ResponseBody
public ResponseVO importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request,
@RequestParam("repoId") Long repoId, @RequestParam("fileName") String fileName) {
Excel Java Excel.java
import java.awt.Color;
import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import acqm.storage.common.constant.Constants;
public class ExcelParseUtils {
private static Logger logger = LogManager.getLogger(ExcelParseUtils.class);
public static void exportExcelFileSheets(InputStream fileInputSteam, List<List<List<String>>> dataList,
String fileName, HttpServletResponse response,Integer sheetCount,List<Integer> removeSheets,
String agent,List<List<Integer>> mergeNum,List<Integer> mergeSheets,
Integer beginSheet,boolean isBeginTwo,boolean isErrorColumn)
throws Exception{
Workbook workbook = new XSSFWorkbook(fileInputSteam);
beginSheet = beginSheet == null ? 0 : beginSheet;
int num = 0;
for (int i = 0; i < sheetCount; i++) {
if(CollectionUtils.isEmpty(removeSheets) || !removeSheets.contains(i)){
if(!CollectionUtils.isEmpty(mergeSheets) && mergeSheets.contains(i)){
workbook = exportExcelSheets(dataList.get(num),i,workbook,mergeNum.get(num - beginSheet),false,false);
}else{
workbook = exportExcelSheets(dataList.get(num),i,workbook,null,isBeginTwo,isErrorColumn);
}
num++;
}
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
fileName = FileUtils.encodeDownloadFilename(fileName, agent);
response.setHeader("Content-Disposition","attachment;filename="+fileName);
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
workbook.close();
response.getOutputStream().close();
}
/**
* 导出多个sheet页
* 根据数据集转化为excel
*
* @param title 表格标题名
* @param headers 表格属性列名数组
* @param list 表格需要显示的数据集合，List<String>的长度要和headers的长度一样
* @throws Exception
*/
public static Workbook exportExcelSheets(
List<List<String>> list,Integer sheetNum,Workbook workbook,
List<Integer> mergeNum,boolean isBeginTwo,boolean isErrorColumn){
XSSFCellStyle borderStyle = (XSSFCellStyle)workbook.createCellStyle();
borderStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
borderStyle.setWrapText(true);
// 设置这些样式
borderStyle.setFillForegroundColor(HSSFColor.WHITE.index);
borderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
borderStyle.setBorderTop(BorderStyle.THIN);
borderStyle.setBorderBottom(BorderStyle.THIN);
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THIN);
// 设置这些样式
borderStyle.setAlignment(HorizontalAlignment.LEFT);
borderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 生成表格中非标题栏的字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("微软雅黑");
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
borderStyle.setFont(font);
// 读取表格的第一个sheet页
Sheet sheet = workbook.getSheetAt(sheetNum);
//合并单元格
if(!CollectionUtils.isEmpty(mergeNum)){
for (int i = 0; i < mergeNum.size(); i++) {
if(i == 0){
if(mergeNum.get(i) - 1 > 1){
sheet.addMergedRegion(new CellRangeAddress(1,mergeNum.get(i),0,0));
}
}else{
if(mergeNum.get(i) - (mergeNum.get(i-1)+1) > 1){
sheet.addMergedRegion(new CellRangeAddress(mergeNum.get(i-1)+1,mergeNum.get(i),0,0));
}
}
}
}
XSSFRow row = null;
// 遍历集合数据，产生数据行
int num = 1;
if(isBeginTwo && sheetNum < 5){
num = 2;
}
if (!isErrorColumn) {
for (int i = 0; i < list.size(); i++) {
row = (XSSFRow) sheet.createRow(i + num);
List<String> stringList = list.get(i);
int size = stringList.size();
for (int j = 0; j < size; j++) {
XSSFCell dataCell = row.createCell(j, Cell.CELL_TYPE_STRING);
dataCell.setCellStyle(borderStyle);
XSSFRichTextString richString = new XSSFRichTextString(stringList.get(j));
dataCell.setCellValue(richString);
}
}
}else {
if (CollectionUtils.isEmpty(list)) {
//若导入的sheet中无错误数据，则需要删除错误类型列
deleteColumn(sheet, 0);
row = (XSSFRow) sheet.getRow(0);
XSSFCell cell = row.getCell(0);
cell.setCellValue(new XSSFRichTextString(Constants.ASSIST_INTERVIEW_QUES_REPO_IMPORT_QUES_DECLARE[sheetNum]));
//找到A1单元格:被合并的单元格
CellReference ref = new CellReference("A1");
for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
// Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
// Cell firstCellOfFirstRow = firstRow.getCell(mergedRegion.getFirstColumn());
if (mergedRegion.getFirstRow() == ref.getRow() && mergedRegion.getLastColumn() == ref.getCol()
+ Constants.ASSIST_INTERVIEW_QUES_REPO_IMPORT_QUES_CELL_NUM_BEFORE_REMOVE[sheetNum]) {
//由于删除第一列后，原有的A1单元格长度与其他行的列长度总和不一致，需将将合并的单元格拆分，再合并成新的单元格
sheet.removeMergedRegion(i);
}
}
borderStyle.setFillForegroundColor(new XSSFColor(new Color(198, 224, 180)));//自定义颜色（淡绿色）：红198，绿色224，蓝180
//重新合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, Constants.ASSIST_INTERVIEW_QUES_REPO_IMPORT_QUES_MERGE_CELL_NUM_AFTER_REMOVE[sheetNum]));
cell.setCellStyle(borderStyle);
}else {
//若sheet中有错误数据，则按照原模板刷出数据
for (int i = 0; i < list.size(); i++) {
row = (XSSFRow) sheet.createRow(i + num);
List<String> stringList = list.get(i);
int size = stringList.size();
for (int j = 0; j < size; j++) {
XSSFCell dataCell = row.createCell(j, Cell.CELL_TYPE_STRING);
dataCell.setCellStyle(borderStyle);
XSSFRichTextString richString = new XSSFRichTextString(stringList.get(j));
dataCell.setCellValue(richString);
}
}
}
}
return workbook;
}
/**
* 解析Excel
*
* @param execelFile
*/
public static List<List<List<String>>> readExcel(InputStream inputStream, Integer startNum, List<Integer> lineNumList) throws Exception {
if (null == inputStream) {
return null;
}
if (startNum == null) {
startNum = 0;
}
List<List<List<String>>> returnList = new ArrayList<>();
byte[] datas = IOUtils.toByteArray(inputStream);
try {
// 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)
Workbook book = null;
try {
// Excel 2007获取方法
book = new XSSFWorkbook(new ByteArrayInputStream(datas));
} catch (Exception ex) {
logger.error(ex);
// Excel 2003获取方法
book = new HSSFWorkbook(new ByteArrayInputStream(datas));
}
//模板校验
if(!modelChecked(book)){
List<List<String>> listOuter = new ArrayList<>();
List<String> list = new ArrayList<>();
list.add("wrongModel");
listOuter.add(list);
returnList.add(listOuter);
return returnList;
}
int sheetNum = 0;
for(Integer lineNum : lineNumList){
List<List<String>> dataList = new ArrayList<>();
// 读取表格的第一个sheet页
Sheet sheet = book.getSheetAt(sheetNum);
// 定义 row
Row row;
// 总共有多少行,从0开始
int totalRows = sheet.getLastRowNum();
// 循环输出表格中的内容,首先循环取出行,再根据行循环取出列
for (int i = startNum; i <= totalRows; i++) {
row = sheet.getRow(i);
// 处理空行
if (row == null) {
dataList.add(null);
continue;
}
List<String> list = new ArrayList<>();
// 总共有多少列,从0开始
int totalCells = row.getLastCellNum();
boolean colFlag = false;
//起始单元格没有输入
if(row.getFirstCellNum() > 0){
for (int h = 0; h < row.getFirstCellNum(); h++) {
list.add(null);
}
}
for (int j = row.getFirstCellNum(); j < totalCells; j++) {
// 处理空列
if (StringUtils.isEmpty(row.getCell(j) == null ? "" : row.getCell(j).toString())) {
list.add(null);
colFlag = colFlag | colFlag;
continue;
} else {
colFlag = true;
list.add(XlsFileParseUtil.convertCell(row.getCell(j)));
}
}
//结束单元格没有输入
if(totalCells < lineNum){
for (int j = 0; j < (lineNum - totalCells); j++) {
list.add(null);
}
}
if (colFlag) {
dataList.add(list);
}
else {
dataList.add(null);
}
}
sheetNum++;
returnList.add(dataList);
// if (!CollectionUtils.isEmpty(dataList)) {
// returnList.add(dataList);
// }
}
} catch (FileNotFoundException e) {
throw new RuntimeException("excel文件不存在", e);
} catch (Exception e) {
throw new RuntimeException("解析excel出错", e);
}
return returnList;
}
public static boolean modelChecked(Workbook book){
boolean modelChecked = true;
Row row;
List<List<String>> rowModelListAll = new ArrayList<List<String>>();
List<String> rowModelListOne = new ArrayList<String>();
List<String> rowModelListTwo = new ArrayList<String>();
List<String> rowModelListThree = new ArrayList<String>();
List<String> rowModelListFour = new ArrayList<String>();
List<String> rowModelListFive = new ArrayList<String>();
String replaceModel = "\\s*|\t|\r|\n";//空格、制表符、回车、换行
//匹配模板名称
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN1);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN2);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN3);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN4);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN5);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN6);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN7);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN8);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN9);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN10);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN11);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN12);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN13);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN14);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN15);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN16);
rowModelListOne.add(Constants.IMPORT_COMMON_QUESTIN_ONE_COLUMN17);
rowModelListAll.add(rowModelListOne);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN1);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN2);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN3);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN4);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN5);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN6);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN7);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN8);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN9);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN10);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN11);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN12);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN13);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN14);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN15);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN16);
rowModelListTwo.add(Constants.IMPORT_COMMON_QUESTIN_TWO_COLUMN17);
rowModelListAll.add(rowModelListTwo);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN1);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN2);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN3);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN4);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN5);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN6);
rowModelListThree.add(Constants.IMPORT_COMMON_QUESTIN_THREE_COLUMN7);
rowModelListAll.add(rowModelListThree);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN1);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN2);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN3);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN4);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN5);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN6);
rowModelListFour.add(Constants.IMPORT_COMMON_QUESTIN_FOUR_COLUMN7);
rowModelListAll.add(rowModelListFour);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN1);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN2);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN3);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN4);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN5);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN6);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN7);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN8);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN9);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN10);
rowModelListFive.add(Constants.IMPORT_COMMON_QUESTIN_FIVE_COLUMN11);
rowModelListAll.add(rowModelListFive);
//校验列名
for (int i = 0; i < 5; i++) {
List<String> rowModelList = rowModelListAll.get(i);
Sheet sheet = book.getSheetAt(i);
row = sheet.getRow(1);
if(row == null){
return false;
}
for (int j = 0; j < rowModelList.size(); j++) {
if(XlsFileParseUtil.convertCell(row.getCell(j)) == null || !rowModelList.get(j).equals(Pattern.compile(replaceModel).matcher(XlsFileParseUtil.convertCell(row.getCell(j))).replaceAll(""))){
modelChecked = false;
return false;
}
}
}
return modelChecked;
}
/**
* 删除列
* @param sheet
* @param columnToDelete
*/
public static void deleteColumn(Sheet sheet, int columnToDelete) {
for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
Row row = sheet.getRow(rId);
for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
Cell cOld = row.getCell(cID);
if (cOld != null) {
row.removeCell(cOld);
}
Cell cNext = row.getCell(cID + 1);
if (cNext != null) {
Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
cloneCell(cNew, cNext);
//Set the column width only on the first row.
//Other wise the second row will overwrite the original column width set previously.
if (rId == 0) {
sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
}
}
}
}
}
/**
* 右边列左移
* @param cNew
* @param cOld
*/
public static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
if (CellType.BOOLEAN == cNew.getCellTypeEnum()) {
cNew.setCellValue(cOld.getBooleanCellValue());
} else if (CellType.NUMERIC == cNew.getCellTypeEnum()) {
cNew.setCellValue(cOld.getNumericCellValue());
} else if (CellType.STRING == cNew.getCellTypeEnum()) {
cNew.setCellValue(cOld.getStringCellValue());
} else if (CellType.ERROR == cNew.getCellTypeEnum()) {
cNew.setCellValue(cOld.getErrorCellValue());
} else if (CellType.FORMULA == cNew.getCellTypeEnum()) {
cNew.setCellValue(cOld.getCellFormula());
}
}
}
Excel read demo
package acqm.storage.common;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
public class XlsFileParseUtil {
private static Log logger = LogFactory.getLog(XlsFileParseUtil.class.getName());
/**
* 传入文件路径获取HSSFWorkbook对象
*
* @param filePath
* @return
*/
public static HSSFWorkbook getWorkBook(String filePath) {
if (StringUtils.isBlank(filePath)) {
return null;
}
File file = new File(filePath);
if (!file.exists()) {
return null;
}
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
return hssfWorkbook;
} catch (FileNotFoundException e) {
logger.info("FileNotFoundException e: " + e);
} catch (IOException e) {
logger.info("IOException e: " + e);
}finally{
try {
if(fileInputStream != null){
fileInputStream.close();
}
} catch (IOException e) {
logger.info("fileInputStream closeException: " + e);
}
}
return null;
}
/**
* 传入输入流获取HSSFWorkbook对象
*
* @param fis
* @return
*/
public static HSSFWorkbook getWorkBook(FileInputStream fis) {
try {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fis);
return hssfWorkbook;
} catch (FileNotFoundException e) {
logger.info("FileNotFoundException e: " + e);
} catch (IOException e) {
logger.info("IOException e: " + e);
}
return null;
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
@SuppressWarnings("deprecation")
public static String getCellValue(HSSFCell cell) {
if (Cell.CELL_TYPE_BLANK == cell.getCellType()) {
return "";
} else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
return String.valueOf(cell.getBooleanCellValue());
} else if (Cell.CELL_TYPE_ERROR == cell.getCellType()) {
return String.valueOf(cell.getErrorCellValue());
} else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
return "";
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
return String.valueOf(cell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
return cell.getStringCellValue();
}
return "";
}
@SuppressWarnings("deprecation")
public static String convertCell(Cell cell) {
NumberFormat formater = NumberFormat.getInstance();
formater.setGroupingUsed(false);
String cellValue = "";
if (cell == null) {
return cellValue;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
cellValue = format.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
} else {
cellValue = formater.format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString();
break;
case HSSFCell.CELL_TYPE_ERROR:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
default:
cellValue = "";
}
//return cellValue.trim();
return cellValue;
}
/**
* copy单元格的值
*
* @param newCell
* @param cell
*/
@SuppressWarnings("deprecation")
public static void setCellValue(HSSFCell newCell, HSSFCell oldCell) {
if (Cell.CELL_TYPE_BOOLEAN == oldCell.getCellType()) {
newCell.setCellValue(oldCell.getBooleanCellValue());
} else if (Cell.CELL_TYPE_ERROR == oldCell.getCellType()) {
newCell.setCellValue(oldCell.getErrorCellValue());
} else if (Cell.CELL_TYPE_NUMERIC == oldCell.getCellType()) {
newCell.setCellValue(oldCell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == oldCell.getCellType()) {
newCell.setCellValue(oldCell.getStringCellValue());
}
}
/**
* 对单元格设置数值类型的值
*
* @param newCell
*/
@SuppressWarnings("deprecation")
public static void setRandomCellValue(HSSFCell newCell) {
newCell.setCellType(Cell.CELL_TYPE_STRING);
newCell.setCellValue(String.valueOf(RandomUtils.nextInt(100000, 999999)));
}
/**
* 对单元格设置数值类型的值 并根据值得余数设置不同背景色
*
* @param newCell
* @param hssfCell
* @param cellStyle
* @param hssfCellStyle2
*/
public static void setRandomCellValue(HSSFCell newCell, HSSFCell hssfCell, HSSFCellStyle cellStyle,
HSSFCellStyle hssfCellStyle2) {
newCell.setCellValue(String.valueOf(RandomUtils.nextLong(10000000L, 99999999L)));
logger.info("cellValue: " + newCell.getStringCellValue());
cellStyle.cloneStyleFrom(hssfCellStyle2);
setCellStyleBackGroundColor(newCell, cellStyle);
newCell.setCellStyle(cellStyle);
}
/**
* 对单元格设置背景色
*
* @param newCell
* @param cellStyle
*/
@SuppressWarnings("deprecation")
private static void setCellStyleBackGroundColor(HSSFCell newCell, HSSFCellStyle cellStyle) {
int total = MathUtil.getTotalNum(newCell.getStringCellValue());
switch (total) {
case 1:
cellStyle.setFillForegroundColor(IndexedColors.VIOLET.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.VIOLET.index);
break;
case 2:
cellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.LEMON_CHIFFON.index);
break;
case 3:
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.YELLOW.index);
break;
case 4:
cellStyle.setFillForegroundColor(IndexedColors.PINK.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.PINK.index);
break;
case 5:
cellStyle.setFillForegroundColor(IndexedColors.ROSE.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.ROSE.index);
break;
case 6:
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.LIGHT_GREEN.index);
break;
case 7:
cellStyle.setFillForegroundColor(IndexedColors.RED.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.RED.index);
break;
case 8:
cellStyle.setFillForegroundColor(IndexedColors.BLUE.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.BLUE.index);
break;
default:
cellStyle.setFillForegroundColor(IndexedColors.GREEN.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
MathUtil.addUp((int) IndexedColors.GREEN.index);
}
}
/**
* 从sheet最后一行不为空开始创建指定行数的row
*
* @param hssfSheet
* @param lastRowNum
* @param i
*/
public static int creatBlankRow(HSSFSheet hssfSheet, int lastRowNum, int num) {
for (int i = 0; i < num; i++) {
hssfSheet.createRow(++lastRowNum);
}
return lastRowNum;
}
public static void creatAddUpRow(HSSFWorkbook hssfWorkbook, HSSFSheet hssfSheet, int lastRowNum) {
if (!MathUtil._colors.isEmpty()) {
Iterator<Integer> colorIndex = MathUtil._colors.keySet().iterator();
while (colorIndex.hasNext()) {
int index = colorIndex.next();
HSSFRow hssfRow = hssfSheet.createRow(++lastRowNum);
setCellBackGroundColor(hssfWorkbook.createCellStyle(), hssfRow.createCell(0), index);
setCellValue(hssfRow.createCell(1), MathUtil._colors.get(index));
}
}
}
@SuppressWarnings("deprecation")
private static void setCellValue(HSSFCell createCell, Integer integer) {
createCell.setCellType(Cell.CELL_TYPE_STRING);
createCell.setCellValue(integer);
}
@SuppressWarnings("deprecation")
private static void setCellBackGroundColor(HSSFCellStyle hssfCellStyle, HSSFCell createCell,
Integer integer) {
hssfCellStyle.setFillForegroundColor(integer.shortValue());
hssfCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
createCell.setCellStyle(hssfCellStyle);
}
/**
* @param destWorkBook 目标workbook
* @param sourceWorkBook 源workbook
* @param sourceSheet 源sheet
* @param targetSheet 目sheet
* @param pStartRow 起始读取行
* @param pEndRow 结束读取行
* @param pPosition 目标保存
*/
@SuppressWarnings("deprecation")
public static void copyRows(HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook,
HSSFSheet sourceSheet, HSSFSheet targetSheet, int pStartRow, int pEndRow, int pPosition) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
int cType;
int i;
int j;
int targetRowFrom;
int targetRowTo;
if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}
List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
oldRanges.add(sourceSheet.getMergedRegion(i));
}
// 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
for (int k = 0; k < oldRanges.size(); k++) {
CellRangeAddress oldRange = oldRanges.get(k);
CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
oldRange.getFirstColumn(), oldRange.getLastColumn());
if (oldRange.getFirstRow() >= pStartRow && oldRange.getLastRow() <= pEndRow) {
targetRowFrom = oldRange.getFirstRow() - pStartRow + pPosition;
targetRowTo = oldRange.getLastRow() - pStartRow + pPosition;
oldRange.setFirstRow(targetRowFrom);
oldRange.setLastRow(targetRowTo);
targetSheet.addMergedRegion(oldRange);
sourceSheet.addMergedRegion(newRange);
}
}
// 设置列宽
for (i = pStartRow; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow != null) {
for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {
targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
targetSheet.setColumnHidden(j, false);
}
break;
}
}
List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();
// 拷贝行并填充数据
for (; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
for (j = sourceRow.getFirstCellNum(); j <= sourceRow.getPhysicalNumberOfCells(); j++) {
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetCell = targetRow.createCell(j);
// 复制样式
// 样式的设置
HSSFCellStyle cStyle = compareStoreAndGetCellStyle(cellStyleList, sourceCell.getCellStyle(),
destWorkBook, sourceWorkBook);
targetCell.setCellStyle(cStyle);
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
// System.out.println("--------TYPE_BOOLEAN:" +
// targetCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
// System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
// parseFormula这个函数的用途在后面说明
targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
// System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
// System.out.println("--------TYPE_NUMERIC:" +
// targetCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
// System.out.println("--------TYPE_STRING:" + i +
// targetCell.getRichStringCellValue());
break;
default:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
// System.out.println("--------TYPE_STRING:" + i +
// targetCell.getRichStringCellValue());
break;
}
}
}
// 分页符的拷贝
int[] rowBreaks = sourceSheet.getRowBreaks();
for (int rowBreaksIndex = 0; rowBreaksIndex < rowBreaks.length; rowBreaksIndex++) {
targetSheet.setRowBreak(pPosition + rowBreaks[rowBreaksIndex]);
}
}
/**
* @param destWorkBook 目标workbook
* @param sourceWorkBook 源workbook
* @param sourceSheet 源sheet
* @param targetSheet 目sheet
* @param pStartRow 起始读取行
* @param pEndRow 结束读取行
* @param pPosition 目标保存
*/
@SuppressWarnings("deprecation")
public static void copyRow(HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook, HSSFSheet sourceSheet,
HSSFSheet targetSheet, int pStartRow, int pEndRow, int pPosition) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
int cType;
int targetColFrom;
int targetColTo;
if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}
List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
oldRanges.add(sourceSheet.getMergedRegion(i));
}
// 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
for (int k = 0; k < oldRanges.size(); k++) {
CellRangeAddress oldRange = oldRanges.get(k);
CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
oldRange.getFirstColumn(), oldRange.getLastColumn());
if (oldRange.getFirstRow() >= pStartRow && oldRange.getLastRow() <= pEndRow) {
targetColFrom = oldRange.getFirstColumn() + pPosition;
targetColTo = oldRange.getLastColumn() + pPosition;
oldRange.setFirstColumn(targetColFrom);
;
oldRange.setLastColumn(targetColTo);
targetSheet.addMergedRegion(oldRange);
sourceSheet.addMergedRegion(newRange);
}
}
/*
* // 设置列宽 for (i = pStartRow; i <= pEndRow; i++) { sourceRow = sourceSheet.getRow(i); if
* (sourceRow != null) { for (j = sourceRow.getLastCellNum(); j >
* sourceRow.getFirstCellNum(); j--) { targetSheet.setColumnWidth(j,
* sourceSheet.getColumnWidth(j)); targetSheet.setColumnHidden(j, false); } break; } }
*/
List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();
// 拷贝行并填充数据
for (int i = pStartRow; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(pStartRow);
targetRow.setHeight(sourceRow.getHeight());
for (int j = sourceRow.getFirstCellNum(); j <= sourceRow.getPhysicalNumberOfCells(); j++) {
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetCell = targetRow.createCell(j + pPosition);
// 复制样式
// 样式的设置
HSSFCellStyle cStyle = compareStoreAndGetCellStyle(cellStyleList, sourceCell.getCellStyle(),
destWorkBook, sourceWorkBook);
targetCell.setCellStyle(cStyle);
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
break;
default:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
break;
}
}
}
// 分页符的拷贝
int[] rowBreaks = sourceSheet.getRowBreaks();
for (int rowBreaksIndex = 0; rowBreaksIndex < rowBreaks.length; rowBreaksIndex++) {
targetSheet.setRowBreak(rowBreaks[rowBreaksIndex]);
}
}
/**
* 比较存储并获得cellstyle
*
* @param cellStyleList
* @param cellStyle
* @param sourceWorkBook
* @return
*/
private static HSSFCellStyle compareStoreAndGetCellStyle(List<HSSFCellStyle> cellStyleList,
HSSFCellStyle sourceCellStyle, HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook) {
for (int index = 0; index < cellStyleList.size(); index++) {
HSSFCellStyle cellStyle = cellStyleList.get(index);
if (isEqual(cellStyle, sourceCellStyle, destWorkBook, sourceWorkBook)) {
return cellStyle;
}
}
// 拷贝新的样式到列表
HSSFCellStyle cStyle = destWorkBook.createCellStyle();
cStyle = copyCellStyle(cStyle, sourceCellStyle, sourceWorkBook);
cellStyleList.add(cStyle);
return cStyle;
}
/**
* 两个cellStyle是否相同
*
* @param cellStyle
* @param sourceCellStyle
* @param sourceWorkBook
* @param destWorkBook
* @return
*/
@SuppressWarnings("deprecation")
private static boolean isEqual(HSSFCellStyle cellStyle, HSSFCellStyle sourceCellStyle,
HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook) {
// 判断换行样式是否一样
if (cellStyle.getWrapText() != sourceCellStyle.getWrapText()) {
return false;
}
// 对齐方式是否一样
if (cellStyle.getAlignment() != sourceCellStyle.getAlignment()) {
return false;
}
if (cellStyle.getVerticalAlignment() != sourceCellStyle.getVerticalAlignment()) {
return false;
}
// 边框是否一样
if (cellStyle.getBorderBottom() != sourceCellStyle.getBorderBottom()) {
return false;
}
if (cellStyle.getBorderLeft() != sourceCellStyle.getBorderLeft()) {
return false;
}
if (cellStyle.getBorderRight() != sourceCellStyle.getBorderRight()) {
return false;
}
if (cellStyle.getBorderTop() != sourceCellStyle.getBorderTop()) {
return false;
}
if (cellStyle.getBottomBorderColor() != sourceCellStyle.getBottomBorderColor()) {
return false;
}
if (cellStyle.getLeftBorderColor() != sourceCellStyle.getLeftBorderColor()) {
return false;
}
if (cellStyle.getRightBorderColor() != sourceCellStyle.getRightBorderColor()) {
return false;
}
if (cellStyle.getTopBorderColor() != sourceCellStyle.getTopBorderColor()) {
return false;
}
// 字体是否一样
HSSFFont sourceFont = sourceCellStyle.getFont(sourceWorkBook);
HSSFFont destFont = cellStyle.getFont(destWorkBook);
if (destFont.getBoldweight() != sourceFont.getBoldweight()) {
return false;
}
if (destFont.getCharSet() != sourceFont.getCharSet()) {
return false;
}
if (destFont.getColor() != sourceFont.getColor()) {
return false;
}
if (destFont.getColor() != sourceFont.getColor()) {
return false;
}
if (destFont.getFontHeight() != sourceFont.getFontHeight()) {
return false;
}
if (destFont.getFontHeightInPoints() != sourceFont.getFontHeightInPoints()) {
return false;
}
if (destFont.getIndex() != sourceFont.getIndex()) {
return false;
}
if (destFont.getItalic() != sourceFont.getItalic()) {
return false;
}
if (destFont.getUnderline() != sourceFont.getUnderline()) {
return false;
}
if (destFont.getStrikeout() != sourceFont.getStrikeout()) {
return false;
}
if (!destFont.getFontName().equals(sourceFont.getFontName())) {
return false;
}
// 别的样式是否一样
return true;
}
/**
* 样式拷贝
*
* @param cStyle 目标style
* @param sourceCellStyle 源style
* @param sourceWorkBook 源workBook
* @return
*/
@SuppressWarnings("deprecation")
private static HSSFCellStyle copyCellStyle(HSSFCellStyle cStyle, HSSFCellStyle sourceCellStyle,
HSSFWorkbook sourceWorkBook) {
if (sourceCellStyle == null || cStyle == null) {
return cStyle;
}
// 是否换行
cStyle.setWrapText(sourceCellStyle.getWrapText());
// 字体拷贝
// cStyle.setFont(sourceCellStyle.getFont(sourceWorkBook));
// cStyle.cloneStyleFrom(sourceCellStyle);
// 拷贝对齐方式
cStyle.setAlignment(sourceCellStyle.getAlignment());
cStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
// 边框拷贝
cStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
cStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
cStyle.setBorderRight(sourceCellStyle.getBorderRight());
cStyle.setBorderTop(sourceCellStyle.getBorderTop());
cStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
cStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
cStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
cStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
// 别的样式的拷贝
return cStyle;
}
/**
* 处理公式
*
* @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();
}
/**
* 复制原有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++) {
CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(mergedRegionAt);
}
}
/**
* 复制单元格
*
* @param srcCell
* @param distCell
* @param copyValueFlag true则连同cell的内容一起复制
*/
@SuppressWarnings("deprecation")
public static void copyCell(HSSFWorkbook wb, HSSFCell srcCell, HSSFCell distCell, boolean copyValueFlag) {
HSSFCellStyle newstyle = wb.createCellStyle();
copyCellStyle(srcCell.getCellStyle(), newstyle);
// distCell.setEncoding(srcCell.get));
// 样式
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
}
}
}
/**
* 复制一个单元格样式到目的单元格样式
*
* @param fromStyle
* @param toStyle
*/
@SuppressWarnings("deprecation")
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());
}
}
