之前写的低版本poi 3.8工具是可以用,但是在导出的时候出现问题
所以还是得用高版本工具,原来低版本链接poi低版本导入excel表头
问题
在处理Excel文件时,您可能已经使用了Apache POI库。然而,在使用过程中,您可能会遇到一个常见的错误:java.lang.NoSuchFieldError:Factory。这个错误通常意味着在运行时环境中存在类路径问题或不同版本的POI库冲突
解决办法
我把之前低版本poi 3.8 兼容删除,把里面工具类都换成5.2
最新的工具类
ExcelColorHelper
package com.njry.utils.excel;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFColor;
public class ExcelColorHelper {
public ExcelColorHelper() {
}
public static String getHex(short colorIndex) {
// 根据颜色索引获取对应的颜色对象
IndexedColors indexedColor = IndexedColors.fromInt(colorIndex);
XSSFColor xssfColor = new XSSFColor(IndexedColors.fromInt(indexedColor.getIndex()), null);
return xssfColor.getARGBHex();
}
public static void main(String[] args)
{
}
}
ExcelHelper
package com.njry.utils.excel;
import jxl.Cell;
import jxl.Sheet;
public class ExcelHelper {
public static String getContents(Sheet sheet, int rowIndex, int colIndex) {
String contents = "";
try {
Cell[] cells = sheet.getRow(rowIndex);
contents = cells[colIndex].getContents().trim();
} catch (Exception e) {
contents = "";
}
return contents;
}
}
ExcelToHtml
package com.njry.utils.excel;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
public class ExcelToHtml {
public static Font headFont;
public static CellStyle headStyle;
static class HeaderCell {
public int rowIndex;
public int colIndex;
public String text;
public float width;
public String bgcolor;
public String fontColor;
public String fontHeight;
public HeaderRegion headerRegion;
public boolean ascDisplay;
public boolean descDisplay;
}
static class HeaderRegion {
public int targetRowFrom;
public int targetRowTo;
public int targetColumnFrom;
public int targetColumnTo;
public String text;
public int rowLength;
public int colLength;
}
public static String getCellStringValue(Cell cell) {
DecimalFormat df = new DecimalFormat();
if(cell == null) {
return "";
}
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue().trim();
case NUMERIC:
try {
return df.parse(String.valueOf(cell.getNumericCellValue())).toString().trim();
}
catch (ParseException e) {
e.printStackTrace();
}
default:
return "";
}
}
public static HeaderCell[][] excelToHtml(MultipartFile file) {
ExcelColorHelper colorHelper = new ExcelColorHelper();
Workbook wb = null;
try {
String fileName = file.getOriginalFilename();
if(fileName.endsWith(".xlsx")) {
wb = new XSSFWorkbook(file.getInputStream());
} else {
wb = new HSSFWorkbook(file.getInputStream());
}
}
catch (Exception ex) {
}
Sheet sheet = wb.getSheetAt(0);
//获取Sheet中的合并单元格信息
HeaderRegion[] headerRegions = new HeaderRegion[sheet.getNumMergedRegions()];
for(int k = 0; k < sheet.getNumMergedRegions(); k++) {
HeaderRegion headerRegion = null;
CellRangeAddress region = sheet.getMergedRegion(k);
headerRegion = new HeaderRegion();
headerRegion.targetRowFrom = region.getFirstRow();
headerRegion.targetRowTo = region.getLastRow();
headerRegion.targetColumnFrom = region.getFirstColumn();
headerRegion.targetColumnTo = region.getLastColumn();
headerRegion.text = getCellStringValue(sheet.getRow(region.getFirstRow()).getCell(region.getFirstColumn()));
headerRegion.colLength = 1 + (region.getLastColumn() - region.getFirstColumn());
headerRegion.rowLength = 1 + (region.getLastRow() - region.getFirstRow());
headerRegions[k] = headerRegion;
}
//获取Sheet中的单元格信息
int rowNum = sheet.getPhysicalNumberOfRows();
int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
HeaderCell[][] headerCells = new HeaderCell[rowNum][cellNum];
Iterator iter = sheet.rowIterator();
for(int i = 0; iter.hasNext(); i++) {
HeaderCell headerCell = null;
Row row = (Row) iter.next();
int cellNums = row.getPhysicalNumberOfCells();
for(int j = 0; j < cellNums; j++) {
headerCell = new HeaderCell();
Cell cell = row.getCell((short) j);
headStyle = cell.getCellStyle();
headFont = wb.getFontAt(cell.getCellStyle().getFontIndex());//得到单元格的字体
headerCell.rowIndex = i;
headerCell.colIndex = j;
headerCell.text = getCellStringValue(cell);
headerCell.width = sheet.getColumnWidth((short) j) / 32;
headerCell.bgcolor = colorHelper.getHex(headStyle.getFillForegroundColor());
headerCell.fontColor = colorHelper.getHex(headFont.getColor());
headerCell.fontHeight = String.valueOf(headFont.getFontHeight()/20);
boolean hasRegion = false;
for(int k = 0; k < headerRegions.length; k++) {
if(i >= headerRegions[k].targetRowFrom && i <= headerRegions[k].targetRowTo
&& j >= headerRegions[k].targetColumnFrom && j <= headerRegions[k].targetColumnTo) {
headerCell.headerRegion = headerRegions[k];
hasRegion = true;
}
}
if(!hasRegion) {
HeaderRegion headerRegion2 = new HeaderRegion();
headerRegion2.targetRowFrom = i;
headerRegion2.targetRowTo = i;
headerRegion2.targetColumnFrom = j;
headerRegion2.targetColumnTo = j;
headerRegion2.text = getCellStringValue(sheet.getRow(i).getCell((short) j));
headerRegion2.colLength = 1;
headerRegion2.rowLength = 1;
headerCell.headerRegion = headerRegion2;
}
headerCell.ascDisplay = (i == headerCell.headerRegion.targetRowFrom && j == headerCell.headerRegion.targetColumnFrom) ? true : false;
headerCell.descDisplay = (i == headerCell.headerRegion.targetRowTo && j == headerCell.headerRegion.targetColumnFrom) ? true : false;
headerCells[i][j] = headerCell;
}
}
return headerCells;
}
/**
* flag -1 :倒序输出 1 正序输出
* @param headerCells
* @param flag
* @return
*/
public static String getHtmlStr(HeaderCell[][] headerCells, int flag) {
if(headerCells == null || headerCells.length == 0) {
return "";
}
int rowNum = headerCells.length;
int cellNum = headerCells[0].length;
String htmlStr = "";
int i = 0;
boolean bool = i < rowNum;
if(flag == -1) {
i = rowNum - 1;
bool = i >= 0;
}
while(bool) {
htmlStr += "<tr>";
for(int j = 0; j < cellNum; j++) {
boolean bool_tem = flag == -1 ? headerCells[i][j].descDisplay : headerCells[i][j].ascDisplay;
if(bool_tem) {
htmlStr += "<th style=\"";
if(!headerCells[i][j].fontColor.equals("000000")) {
if(!headerCells[i][j].fontColor.equals("")) {
htmlStr += " color :" + headerCells[i][j].fontColor + ";";
}
}
htmlStr += "\"";
if(!headerCells[i][j].bgcolor.equals("FFFFFF")) {
if(!headerCells[i][j].bgcolor.equals("")) {
htmlStr += " bgcolor =\"" + headerCells[i][j].bgcolor + "\"";
}
}
if (headerCells[i][j].headerRegion.colLength <= 1) {
htmlStr += " width =\"" + headerCells[i][j].width + "\"";
}
if(headerCells[i][j].headerRegion.colLength > 1) {
htmlStr += " colspan=" + headerCells[i][j].headerRegion.colLength + " ";
}
if(headerCells[i][j].headerRegion.rowLength > 1) {
htmlStr += " rowspan=" + headerCells[i][j].headerRegion.rowLength + "";
}
htmlStr += ">" + headerCells[i][j].headerRegion.text + "</th> ";
}
}
htmlStr += "</tr>";
if(flag == -1) {
i--;
bool = i >= 0;
} else {
i++;
bool = i < rowNum;
}
}
return htmlStr;
}
public static Map<String, String> getHtmlMap(MultipartFile file) {
HeaderCell[][] headerCell = excelToHtml(file);
Map<String, String> htmlMap = new Hashtable<String, String>();
htmlMap.put("html", getHtmlStr(headerCell, 1));
return htmlMap;
}
}
ExcelToHtmlHead
package com.njry.utils.excel;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Map;
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.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
public class ExcelToHtmlHead {
public static Font headFont;
public static CellStyle headStyle;
static class HeaderCell {
public int rowIndex;
public int colIndex;
public String text;
public float width;
public String bgcolor;
public String fontColor;
public String fontHeight;
public HeaderRegion headerRegion;
public boolean ascDisplay;
public boolean descDisplay;
}
static class HeaderRegion {
public int targetRowFrom;
public int targetRowTo;
public int targetColumnFrom;
public int targetColumnTo;
public String text;
public int rowLength;
public int colLength;
}
public static String getCellStringValue(Cell cell) {
DecimalFormat df = new DecimalFormat();
if(cell == null) {
return "";
}
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue().trim();
case NUMERIC:
try {
return df.parse(String.valueOf(cell.getNumericCellValue())).toString().trim();
}
catch (ParseException e) {
e.printStackTrace();
}
default:
return "";
}
}
public static HeaderCell[][] excelToHtml(MultipartFile file) {
ExcelColorHelper colorHelper = new ExcelColorHelper();
Workbook wb = null;
try {
String fileName = file.getOriginalFilename();
if(fileName.endsWith(".xlsx")) {
wb = new XSSFWorkbook(file.getInputStream());
} else {
wb = new HSSFWorkbook(file.getInputStream());
}
}
catch (Exception ex) {
}
Sheet sheet = wb.getSheetAt(0);
//获取Sheet中的合并单元格信息
HeaderRegion[] headerRegions = new HeaderRegion[sheet.getNumMergedRegions()];
for(int k = 0; k < sheet.getNumMergedRegions(); k++) {
HeaderRegion headerRegion = null;
CellRangeAddress region = sheet.getMergedRegion(k);
headerRegion = new HeaderRegion();
headerRegion.targetRowFrom = region.getFirstRow();
headerRegion.targetRowTo = region.getLastRow();
headerRegion.targetColumnFrom = region.getFirstColumn();
headerRegion.targetColumnTo = region.getLastColumn();
headerRegion.text = getCellStringValue(sheet.getRow(region.getFirstRow()).getCell(region.getFirstColumn()));
headerRegion.colLength = 1 + (region.getLastColumn() - region.getFirstColumn());
headerRegion.rowLength = 1 + (region.getLastRow() - region.getFirstRow());
headerRegions[k] = headerRegion;
}
//获取Sheet中的单元格信息
int rowNum = sheet.getPhysicalNumberOfRows();
int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
HeaderCell[][] headerCells = new HeaderCell[rowNum][cellNum];
Iterator iter = sheet.rowIterator();
for(int i = 0; iter.hasNext(); i++) {
HeaderCell headerCell = null;
Row row = (Row) iter.next();
int cellNums = row.getPhysicalNumberOfCells();
for(int j = 0; j < cellNums; j++) {
headerCell = new HeaderCell();
Cell cell = row.getCell((short) j);
headStyle = cell.getCellStyle();
headFont = wb.getFontAt(cell.getCellStyle().getFontIndex());//得到单元格的字体
headerCell.rowIndex = i;
headerCell.colIndex = j;
headerCell.text = getCellStringValue(cell);
headerCell.width = sheet.getColumnWidth((short) j) / 32;
headerCell.bgcolor = colorHelper.getHex(headStyle.getFillForegroundColor());
headerCell.fontColor = colorHelper.getHex(headFont.getColor());
headerCell.fontHeight = String.valueOf(headFont.getFontHeight()/20);
boolean hasRegion = false;
for(int k = 0; k < headerRegions.length; k++) {
if(i >= headerRegions[k].targetRowFrom && i <= headerRegions[k].targetRowTo
&& j >= headerRegions[k].targetColumnFrom && j <= headerRegions[k].targetColumnTo) {
headerCell.headerRegion = headerRegions[k];
hasRegion = true;
}
}
if(!hasRegion) {
HeaderRegion headerRegion2 = new HeaderRegion();
headerRegion2.targetRowFrom = i;
headerRegion2.targetRowTo = i;
headerRegion2.targetColumnFrom = j;
headerRegion2.targetColumnTo = j;
headerRegion2.text = getCellStringValue(sheet.getRow(i).getCell((short) j));
headerRegion2.colLength = 1;
headerRegion2.rowLength = 1;
headerCell.headerRegion = headerRegion2;
}
headerCell.ascDisplay = (i == headerCell.headerRegion.targetRowFrom && j == headerCell.headerRegion.targetColumnFrom) ? true : false;
headerCell.descDisplay = (i == headerCell.headerRegion.targetRowTo && j == headerCell.headerRegion.targetColumnFrom) ? true : false;
headerCells[i][j] = headerCell;
}
}
return headerCells;
}
/**
* flag -1 :倒序输出 1 正序输出
* @param headerCells
* @param flag
* @return
*/
public static String getHtmlStr(HeaderCell[][] headerCells, int flag) {
if(headerCells == null || headerCells.length == 0) {
return "";
}
int rowNum = headerCells.length;
int cellNum = headerCells[0].length;
String htmlStr = "";
String cellStr = "";
int i = 0;
boolean bool = i < rowNum;
if(flag == -1) {
i = rowNum - 1;
bool = i >= 0;
}
while(bool) {
htmlStr += "<tr>";
cellStr += "";
for(int j = 0; j < cellNum; j++) {
boolean bool_tem = flag == -1 ? headerCells[i][j].descDisplay : headerCells[i][j].ascDisplay;
if(bool_tem) {
htmlStr += "<th";
if (headerCells[i][j].headerRegion.colLength <= 1) {
float width = headerCells[i][j].width;
htmlStr += " width=\"" + new Float(width).intValue() + "\"";
//htmlStr += " nowrap ";
}
if(headerCells[i][j].headerRegion.colLength > 1) {
htmlStr += " colspan=" + headerCells[i][j].headerRegion.colLength + " ";
}
if(headerCells[i][j].headerRegion.rowLength > 1) {
htmlStr += " rowspan=" + headerCells[i][j].headerRegion.rowLength + "";
}
htmlStr += ">" + headerCells[i][j].headerRegion.text + "</th> ";
if(i == rowNum - 1)
{
cellStr += headerCells[i][j].headerRegion.text + ">>" + j + ">>" + headerCells[i][j].width + "#";
}
}
}
htmlStr += "</tr>";
if(flag == -1) {
i--;
bool = i >= 0;
} else {
i++;
bool = i < rowNum;
}
}
return htmlStr + "@" + cellStr;
}
public static Map<String, String> getHtmlMap(MultipartFile file) {
HeaderCell[][] headerCell = excelToHtml(file);
Map<String, String> htmlMap = new Hashtable<String, String>();
htmlMap.put("html", getHtmlStr(headerCell, 1).split("@")[0]);
htmlMap.put("cell", getHtmlStr(headerCell, 1).split("@")[1]);
return htmlMap;
}
}
HssfHelper
package com.njry.utils.excel;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import com.njry.utils.DateHelper;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;;
@SuppressWarnings("deprecation")
public class HssfHelper {
public HSSFFont headFont;
public HSSFCellStyle headStyle;
public HSSFWorkbook wb;
public final Logger logger = Logger.getLogger(this.getClass());
@SuppressWarnings("deprecation")
public HssfHelper() {
wb = new HSSFWorkbook();
headFont = wb.createFont();
headFont.setBold(true);
headFont.setColor(IndexedColors.BLACK.index);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setAlignment(HorizontalAlignment.CENTER);
}
/**
*
* @param wb
* excel文件
* @param row
* 行
* @param column
* 列号
* @param align
* 横向对齐方式
* @param valign
* 纵向对齐方式
* @return 格式化后的单元格
*/
public HSSFCell createCell(HSSFWorkbook wb, HSSFRow row, int column,
int align, int valign) {
HSSFCell cell = row.createCell((short) column);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.forInt((short) align));
cellStyle.setVerticalAlignment(VerticalAlignment.forInt((short) valign));
cell.setCellStyle(cellStyle);
//cell.setEncoding((short) 1); // 支持中文导出
return cell;
}
/**
* 缺省对齐方式为:居中
*
* @param wb
* @param row
* @param column
* @return
*/
public HSSFCell createCenterMiddleCell(HSSFWorkbook wb, HSSFRow row,
int column) {
HSSFCell cell = row.createCell((short) column);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
//cell.setEncoding((short) 1); // 支持中文导出
return cell;
}
public HSSFCell createCell(HSSFWorkbook wb, HSSFRow row, int column) {
HSSFCell cell = row.createCell((short) column);
//cell.setEncoding((short) 1); // 支持中文导出
return cell;
}
public static void main(String args[]) throws Exception {
HssfHelper hssfHelper = new HssfHelper();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 2);
// HSSFCell cell=hssfHelper.createCell(wb, row,
// 0,HSSFCellStyle.ALIGN_CENTER,HSSFCellStyle.VERTICAL_CENTER);
HSSFCell cell = hssfHelper.createCell(wb, row, 2);
cell.setCellValue("中文测试");
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
/**
*
* @param columnIndex
* 列号(从0开始)
* @param columnWidth
* 列宽
*/
public void setColumnWidth(HSSFSheet sheet, int columnIndex, int columnWidth) {
sheet.setColumnWidth((short) columnIndex, (short) (35.7 * columnWidth));
}
public HSSFFont createFont(HSSFWorkbook wb, short boldWeight, short color) {
HSSFFont font = wb.createFont();
if (boldWeight != -1)
// 直接给true
font.setBold(true);
if (color != -1)
font.setColor(color);
return font;
}
public HSSFCellStyle createCellStyle(HSSFWorkbook wb, HSSFFont font,
short valign, short align) {
HSSFCellStyle cellStyle1 = wb.createCellStyle();
if (font != null)
cellStyle1.setFont(font);
if (valign != -1)
cellStyle1.setVerticalAlignment(VerticalAlignment.forInt(valign));
if (align != -1)
cellStyle1.setAlignment(HorizontalAlignment.forInt(align));
return cellStyle1;
}
public void merge(HSSFSheet sheet, int row1, int col1, int row2, int col2) {
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2);
sheet.addMergedRegion(region);
}
public HSSFRow createRow(HSSFSheet sheet, int rowIndex) {
HSSFRow row = sheet.createRow(rowIndex);
return row;
}
public String getCellStringValue(HSSFCell cell) {
DecimalFormat df = new DecimalFormat();
if (cell == null)
return "";
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue().trim();
case NUMERIC:
// 判断是否是日期型的单元格
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return new DateHelper().getDateString(cell.getDateCellValue(),
"yyyy-MM-dd HH:mm:ss");
} else {
try {
return df.parse(String.valueOf(cell.getNumericCellValue()))
.toString().trim();
} catch (ParseException e) {
e.printStackTrace();
}
}
default:
return "";
}
}
}
PoiHelper
package com.njry.utils.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import com.njry.utils.DateHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class PoiHelper {
public Font headFont;
public CellStyle headStyle;
public Workbook wb;
/**
* 构造函数
*/
public PoiHelper() {
wb = new HSSFWorkbook();
headFont = wb.createFont();
headFont.setBold(true);
headFont.setColor(IndexedColors.BLACK.index);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setAlignment(HorizontalAlignment.CENTER);
}
/**
* 构造函数
* @param office_type office类型【2003 || 2007】
*/
public PoiHelper(String office_type) {
if(office_type.equals("2003")){
wb = new HSSFWorkbook();
} else if(office_type.equals("2007")){
wb = new XSSFWorkbook();
}
headFont = wb.createFont();
headFont.setBold(true);
headFont.setColor(IndexedColors.BLACK.index);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setAlignment(HorizontalAlignment.CENTER);
}
/**
* 创建workbook
* @param file
* @return
* @throws IOException
*/
public Workbook createWorkBook(File file, String fileName) throws IOException {
if(fileName.toLowerCase().endsWith("xls")){
return new HSSFWorkbook(new FileInputStream(file));
}
if(fileName.toLowerCase().endsWith("xlsx")){
return new XSSFWorkbook(new FileInputStream(file));
}
return null;
}
/**
* 创建单元格
* @param wb excel文件
* @param row 行
* @param column 列号
* @param align 横向对齐方式
* @param valign 纵向对齐方式
* @return 格式化后的单元格
*/
public Cell createCell(Workbook wb, Row row, int column,
int align, int valign) {
Cell cell = row.createCell(column);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.forInt((short) align));
cellStyle.setVerticalAlignment(VerticalAlignment.forInt((short) valign));
cell.setCellStyle(cellStyle);
return cell;
}
/**
* 缺省对齐方式为:居中
* @param wb
* @param row
* @param column
* @return
*/
public Cell createCenterMiddleCell(Workbook wb, Row row,
int column) {
Cell cell = row.createCell(column);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
return cell;
}
public Cell createCell(Workbook wb, Row row, int column) {
Cell cell = row.createCell(column);
return cell;
}
public static void main(String args[]) throws Exception {
PoiHelper poiHelper = new PoiHelper();
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow((short) 2);
Cell cell = poiHelper.createCell(wb, row, 2);
cell.setCellValue("中文测试");
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
/**
*
* @param columnIndex 列号(从0开始)
* @param columnWidth 列宽
*/
public void setColumnWidth(Sheet sheet, int columnIndex, int columnWidth) {
sheet.setColumnWidth((short) columnIndex, (short) (35.7 * columnWidth));
}
public Font createFont(Workbook wb, short boldWeight, short color) {
Font font = wb.createFont();
if (boldWeight != -1)
font.setBold(true);
if (color != -1)
font.setColor(color);
return font;
}
public CellStyle createCellStyle(Workbook wb, Font font,
short valign, short align) {
CellStyle cellStyle1 = wb.createCellStyle();
if (font != null)
cellStyle1.setFont(font);
if (valign != -1)
cellStyle1.setVerticalAlignment(VerticalAlignment.forInt(valign));
if (align != -1)
cellStyle1.setAlignment(HorizontalAlignment.forInt(align));
return cellStyle1;
}
public void merge(Sheet sheet, int row1, int col1, int row2, int col2) {
sheet.addMergedRegion(new CellRangeAddress(row1, (short) col1, row2, (short) col2));
}
public Row createRow(Sheet sheet, int rowIndex) {
Row row = sheet.createRow(rowIndex);
return row;
}
public String getCellStringValue(Cell cell) {
DecimalFormat df = new DecimalFormat();
if (cell == null)
return "";
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue().trim();
case NUMERIC:
// 判断是否是日期型的单元格
if (DateUtil.isCellDateFormatted(cell)) {
return new DateHelper().getDateString(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
} else {
try {
return df.parse(String.valueOf(cell.getNumericCellValue())).toString().trim();
} catch (ParseException e) {
e.printStackTrace();
}
}
default:
return "";
}
}
public String getCellCode(int index) {
String rows = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (index / 25 < 1) {
return String.valueOf(rows.charAt(index));
} else {
int cj = index / 26;
if (cj == 0) {
return String.valueOf(rows.charAt(index));
}
int mod = index % 26;
return String.valueOf(rows.charAt(cj - 1))
+ String.valueOf(rows.charAt(mod));
}
}
}
XssfHelper
package com.njry.utils.excel;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import com.njry.utils.DateHelper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@SuppressWarnings("unchecked")
public class XssfHelper {
public XSSFFont headFont;
public XSSFCellStyle headStyle;
public XSSFWorkbook xb;
public XssfHelper() {
xb = new XSSFWorkbook();
headFont = xb.createFont();
headFont.setBold(true);
// headFont.setColor(XSSFColor.BLACK.index);
headStyle = xb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setAlignment(HorizontalAlignment.CENTER);
}
/**
*
* @param xb
* excel文件
* @param row
* 行
* @param column
* 列号
* @param align
* 横向对齐方式
* @param valign
* 纵向对齐方式
* @return 格式化后的单元格
*/
public XSSFCell createCell(XSSFWorkbook xb, XSSFRow row, int column,
int align, int valign) {
XSSFCell cell = row.createCell((short) column);
XSSFCellStyle cellStyle = xb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.forInt((short) align));
cellStyle.setVerticalAlignment(VerticalAlignment.forInt((short) valign));
cell.setCellStyle(cellStyle);
// cell.setEncoding((short) 1); // 支持中文导出
return cell;
}
/**
* 缺省对齐方式为:居中
*
* @param xb
* @param row
* @param column
* @return
*/
public XSSFCell createCenterMiddleCell(XSSFWorkbook xb, XSSFRow row,
int column) {
XSSFCell cell = row.createCell((short) column);
XSSFCellStyle cellStyle = xb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
// cell.setEncoding((short) 1); // 支持中文导出
return cell;
}
public XSSFCell createCell(XSSFWorkbook xb, XSSFRow row, int column) {
XSSFCell cell = row.createCell((short) column);
// cell.setEncoding((short) 1); // 支持中文导出
return cell;
}
public static void main(String args[]) throws Exception {
XssfHelper xssfHelper = new XssfHelper();
XSSFWorkbook xb = new XSSFWorkbook();
XSSFSheet sheet = xb.createSheet("new sheet");
XSSFRow row = sheet.createRow((short) 2);
// HSSFCell cell=hssfHelper.createCell(wb, row,
// 0,HSSFCellStyle.ALIGN_CENTER,HSSFCellStyle.VERTICAL_CENTER);
XSSFCell cell = xssfHelper.createCell(xb, row, 2);
cell.setCellValue("中文测试");
FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
xb.write(fileOut);
fileOut.close();
}
/**
*
* @param columnIndex
* 列号(从0开始)
* @param columnWidth
* 列宽
*/
public void setColumnWidth(XSSFSheet sheet, int columnIndex, int columnWidth) {
sheet.setColumnWidth((short) columnIndex, (short) (35.7 * columnWidth));
}
public XSSFFont createFont(XSSFWorkbook xb, short boldWeight, short color) {
XSSFFont font = xb.createFont();
if (boldWeight != -1)
font.setBold(true);
if (color != -1)
font.setColor(color);
return font;
}
public XSSFCellStyle createCellStyle(XSSFWorkbook xb, XSSFFont font,
short valign, short align) {
XSSFCellStyle cellStyle1 = xb.createCellStyle();
if (font != null)
cellStyle1.setFont(font);
if (valign != -1)
cellStyle1.setVerticalAlignment(VerticalAlignment.forInt(valign));
if (align != -1)
cellStyle1.setAlignment(HorizontalAlignment.forInt(align));
return cellStyle1;
}
public void merge(XSSFSheet sheet, int row1, int col1, int row2, int col2) {
sheet.addMergedRegion(new CellRangeAddress(row1, (short) col1, row2,(short) col2));
}
public XSSFRow createRow(XSSFSheet sheet, int rowIndex) {
XSSFRow row = sheet.createRow(rowIndex);
return row;
}
public String getCellStringValue(XSSFCell cell) {
DecimalFormat df = new DecimalFormat();
if (cell == null)
return "";
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue().trim();
case NUMERIC:
// 判断是否是日期型的单元格HSSFDateUtil.isCellDateFormatted(cell)
if (DateUtil.isCellDateFormatted(cell)) {
return new DateHelper().getDateString(cell.getDateCellValue(),
"yyyy-MM-dd HH:mm:ss");
} else {
try {
return df.parse(String.valueOf(cell.getNumericCellValue()))
.toString().trim();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
default:
return "";
}
}
public String getCellCode(int index) {
String rows = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (index / 25 < 1) {
return String.valueOf(rows.charAt(index));
} else {
int cj = index / 26;
if (cj == 0) {
return String.valueOf(rows.charAt(index));
}
int mod = index % 26;
return String.valueOf(rows.charAt(cj - 1))
+ String.valueOf(rows.charAt(mod));
}
}
public XSSFCell createHeadCell(XSSFWorkbook xb, XSSFRow row, int column) {
XSSFFont headFont = xb.createFont();
headFont.setBold(true);
headFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle headStyle = xb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setAlignment(HorizontalAlignment.CENTER);
XSSFCell cell = row.createCell((short) column);
//cell.setEncoding((short) 1); // 支持中文导出
cell.setCellStyle(headStyle);
return cell;
}
}
测试controller(依旧是之前的里面代码,但在colorHelper使用稍有不同,不过就是从工具类到controller的poi都换成5.2)
package com.njry.modules.system.rest;
import com.njry.domain.vo.HeaderCell;
import com.njry.domain.vo.HeaderRegion;
import com.njry.modules.system.domain.SysTest;
import com.njry.modules.system.service.SysTestService;
import com.njry.utils.SecurityUtils;
import com.njry.utils.excel.ExcelColorHelper;
import com.njry.utils.excel.HssfHelper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.util.*;
@RestController
@RequiredArgsConstructor
@Api(tags = "工具:本地存储管理")
@RequestMapping("/api/localStorageExcel")
public class testExcelController {
private final SysTestService sysTestService;
@PostMapping
@ApiOperation("上传文件")
public ResponseEntity<Object> createFile(@RequestParam String name, @RequestParam("file") MultipartFile file){
// 保存导入表头配置
Map<String, String> mp = new HashMap<String, String>();
int res = 1;
// 系统获取的用户名
String currentUsername = SecurityUtils.getCurrentUsername();
try {
if (file == null || file.isEmpty()) {
res = -1;
}
HeaderCell[][] headerCells = excelToHtml(file);
Map cellMap = getHtmlStr(headerCells, 1);//传1 正序
// 获取表头的后处理父子级关系
List cellList = (List)cellMap.get("cellList");
// 只有正序的时候才能这样使用
for(int i = 0; i < cellList.size(); i++){
HashMap<String,Object> hashMap = (HashMap)cellList.get(i);
if(hashMap.get("rowfrom").equals(0)){
hashMap.put("parentLevel",null);
}else{
// 判断不是第一行起的父级是谁
int rowfrom = convertObjectToInt(hashMap.get("rowfrom")) - 1;
int colfrom = convertObjectToInt(hashMap.get("colfrom"));
for (int j = 0; j < cellList.size(); j++) {
HashMap<String,Object> hashMapInner = (HashMap)cellList.get(j);
int rowfrom1 = convertObjectToInt(hashMapInner.get("rowfrom"));
int rowto = convertObjectToInt(hashMapInner.get("rowto"));
int colfrom1 = convertObjectToInt(hashMapInner.get("colfrom"));
int colto = convertObjectToInt(hashMapInner.get("colto"));
if(rowfrom >= rowfrom1 && rowfrom <= rowto && colfrom >= colfrom1 && colfrom <= colto){
hashMap.put("parentLevel",hashMapInner.get("headid"));
}
}
}
}
System.out.println(cellList);
for (int k = 0; k < cellList.size(); k++) {
HashMap<String,Object> hashMap = (HashMap)cellList.get(k);
SysTest resources = new SysTest();
resources.setColfrom((Integer) hashMap.get("colfrom"));
resources.setColto((Integer) hashMap.get("colto"));
resources.setColspan((Integer) hashMap.get("colspan"));
resources.setRowfrom((Integer) hashMap.get("rowfrom"));
resources.setRowto((Integer) hashMap.get("rowto"));
resources.setRowspan((Integer) hashMap.get("rowspan"));
resources.setTitle((String) hashMap.get("title"));
resources.setHeadid((String) hashMap.get("headid"));
resources.setParentlevel((String) hashMap.get("parentLevel"));
sysTestService.create(resources);
}
}catch (Exception e)
{
res = 0;
}
mp.put("res", res + "");
return new ResponseEntity<>(HttpStatus.CREATED);
}
public int convertObjectToInt(Object obj) {
if (obj instanceof String) {
return Integer.parseInt((String) obj);
} else if (obj instanceof Integer) {
return (Integer) obj;
} else {
throw new IllegalArgumentException("Object cannot be converted to int");
}
}
/**
* @description 解析表头
* @author 孙骏 2015-10-23
* @param file
* @return
*/
@SuppressWarnings({"deprecation", "unchecked", "static-access"})
public HeaderCell[][] excelToHtml(MultipartFile file)
{
//开始解析excel
HssfHelper hssfHelper = new HssfHelper();
ExcelColorHelper colorHelper = new ExcelColorHelper();
HSSFWorkbook wb = null;
Font headFont;
CellStyle headStyle;
try
{
wb = new HSSFWorkbook(file.getInputStream());
}
catch (Exception ex)
{
}
HSSFSheet sheet = wb.getSheetAt(0);
//获取Sheet中的合并单元格信息(为下文HeaderCell下属性判断使用)
HeaderRegion[] headerRegions = new HeaderRegion[sheet.getNumMergedRegions()];
for(int k = 0; k < sheet.getNumMergedRegions(); k++)
{
HeaderRegion headerRegion = null;
CellRangeAddress region = sheet.getMergedRegion(k);
headerRegion = new HeaderRegion();
headerRegion.setTargetRowFrom(region.getFirstRow());
headerRegion.setTargetRowTo(region.getLastRow());
headerRegion.setTargetColumnFrom(region.getFirstColumn());
headerRegion.setTargetColumnTo(region.getLastColumn());
headerRegion.setText(hssfHelper.getCellStringValue(sheet.getRow(region.getFirstRow()).getCell(region.getFirstColumn())));
headerRegion.setColLength(1 + (region.getLastColumn() - region.getFirstColumn()));
headerRegion.setRowLength(1 + (region.getLastRow() - region.getFirstRow()));
headerRegions[k] = headerRegion;
}
//获取Sheet中的单元格信息
int rowNum = sheet.getPhysicalNumberOfRows();
// 获取第一行,得到第一行有多少列,就是excel有多少列
int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
HeaderCell[][] headerCells = new HeaderCell[rowNum][cellNum];
Iterator iter = sheet.rowIterator();
for(int i = 0; iter.hasNext(); i++)
{
HeaderCell headerCell = null;
HSSFRow row = (HSSFRow) iter.next();
// 获取每一行高度
float heightInPoints = row.getHeightInPoints();
// 获取每一行有多少列 physical物理的
int cellNums = row.getPhysicalNumberOfCells();
for(int j = 0; j < cellNums; j++)
{
headerCell = new HeaderCell();
// 获取到每一行下每一列
HSSFCell cell = row.getCell((short) j);
headStyle = cell.getCellStyle();
headFont = wb.getFontAt(cell.getCellStyle().getFontIndex());//得到单元格的字体
headerCell.setRowIndex(i);
headerCell.setColIndex(j);
headerCell.setText(hssfHelper.getCellStringValue(cell));
headerCell.setWidth(sheet.getColumnWidth((short) j) / 32);
// Color fillForegroundColorColor = headStyle.getFillForegroundColorColor();
short color = headFont.getColor();
short fillForegroundColor = headStyle.getFillForegroundColor();
headerCell.setBgcolor(colorHelper.getHex(fillForegroundColor));
headerCell.setFontColor(colorHelper.getHex(color));
headerCell.setFontHeight(String.valueOf(headFont.getFontHeight()/20));
boolean hasRegion = false;
for(int k = 0; k < headerRegions.length; k++)
{
// 判断当前cell是否属于合并单元格 i 在合并单元格之内 同时 j 在合并单元格内(上文headerRegions使用)
if(i >= headerRegions[k].getTargetRowFrom() && i <= headerRegions[k].getTargetRowTo()
&& j >= headerRegions[k].getTargetColumnFrom() && j <= headerRegions[k].getTargetColumnTo())
{
headerCell.setHeaderRegion(headerRegions[k]);
hasRegion = true;
}
}
if(!hasRegion)
{
HeaderRegion headerRegion2 = new HeaderRegion();
headerRegion2.setTargetRowFrom(i);
headerRegion2.setTargetRowTo(i);
headerRegion2.setTargetColumnFrom(j);
headerRegion2.setTargetColumnTo(j);
headerRegion2.setText(hssfHelper.getCellStringValue(sheet.getRow(i).getCell((short) j)));
headerRegion2.setColLength(1);
headerRegion2.setRowLength(1);
headerCell.setHeaderRegion(headerRegion2);
}
// 通过!hasRegion将没有合并的单元格都也有headerRegion,就是自己本身,本身的话下面设置两个变量都是true
// 合并区域的开始行i 和开始列 j 是否是当前单元格的开始行和列
headerCell.setAscDisplay((i == headerCell.getHeaderRegion().getTargetRowFrom() && j == headerCell.getHeaderRegion().getTargetColumnFrom()) ? true : false);
// 合并区域的结束行i 和开始列 j 是否是当前单元格的开始行和列
headerCell.setDescDisplay((i == headerCell.getHeaderRegion().getTargetRowTo() && j == headerCell.getHeaderRegion().getTargetColumnFrom()) ? true : false);
headerCells[i][j] = headerCell;
}
}
return headerCells;
}
/**
* @description 拼接表头样式(报表表头导入)
* @author 孙骏 2015-10-23
* @param headerCells
* @param flag
* @return
*/
@SuppressWarnings("unchecked")
public Map getHtmlStr(HeaderCell[][] headerCells, int flag) {
if(headerCells == null || headerCells.length == 0) {
return null;
}
Map map = new HashMap();
int rowNum = headerCells.length;
int cellNum = headerCells[0].length;
String htmlStr = "";
String cellStr = "";
List cellList = new ArrayList();
int width = 0;
Map cellMap = null;
int i = 0;
boolean bool = i < rowNum;
if(flag == -1)
{
i = rowNum - 1;
bool = i >= 0;
}
while(bool)
{
for(int j = 0; j < cellNum; j++)
{
// 通过是否是正序(合并单元格开始行,限制列仅为开始)或者倒序,可以排除合并中不是开始的单元格(不必要的循环)
boolean bool_tem = flag == -1 ? headerCells[i][j].isDescDisplay(): headerCells[i][j].isAscDisplay();
if(bool_tem)
{
// 不是倒数第一和第二行循环
// 添加随机id,方便后面建立父子级关系
String uuid = UUID.randomUUID().toString().replaceAll("-", "");
if((i != rowNum - 2) && (i != rowNum - 1))
{
cellMap = new HashMap();
cellMap.put("title", headerCells[i][j].getText()); //标题
cellMap.put("headid", uuid); //唯一id
cellMap.put("rowfrom", headerCells[i][j].getHeaderRegion().getTargetRowFrom()); //起始行
cellMap.put("colfrom", headerCells[i][j].getHeaderRegion().getTargetColumnFrom()); //起始列
cellMap.put("rowto", headerCells[i][j].getHeaderRegion().getTargetRowTo()); //目标行
cellMap.put("colto", headerCells[i][j].getHeaderRegion().getTargetColumnTo()); //目标列
cellMap.put("colspan", headerCells[i][j].getHeaderRegion().getColLength()); //合并列
cellMap.put("rowspan", headerCells[i][j].getHeaderRegion().getRowLength()); //合并行
cellMap.put("width", headerCells[i][j].getWidth()); //合并行
cellList.add(cellMap);
}
if(i == rowNum - 2) //标题对应英文字段(总行数rowNum不变,i变化,总函数减去2就是倒数第二行)
{
htmlStr += "#" + headerCells[i][j].getHeaderRegion().getText() + ">>" + j;
width += new Float(headerCells[i][j].getWidth()).intValue() + 20;
}
else if(i == rowNum - 1) //英文字段类型
{
cellStr += "#" + headerCells[i][j].getHeaderRegion().getText() + ">>" + j;
}
}
}
if(flag == -1)
{
i--;
bool = i >= 0;
}
else
{
i++;
bool = i < rowNum;
}
}
map.put("htmlStr", htmlStr.substring(1));
map.put("cellStr", cellStr.substring(1));
map.put("cellList", cellList);
map.put("width", width);
return map;
}
}