定义两个实体类HeaderCell和HeaderRegion
package com.njry.domain.vo;
import lombok.Data;
/**
* @description 标题的单元格属性
* @author 大佬
* @date 2015-10-16
*/
@Data
public class HeaderCell
{
private int rowIndex; //单元格第几行
private int colIndex; //单元格第几列
private String text; //单元格的值
private float width; //单元格宽度
private String bgcolor; //单元格背景色
private String fontColor; //字体颜色
private String fontHeight; //字体大小
private HeaderRegion headerRegion; //合并单元格信息
// private boolean isRegion; //是否属于合并单元格
private boolean ascDisplay; //正序输出
private boolean descDisplay; //倒序输出
}
package com.njry.domain.vo;
import lombok.Data;
/**
* @description excel的sheet中的合并单元格信息
* @author 大佬
* @date 2015-10-16
*/
@Data
public class HeaderRegion
{
private int targetRowFrom; //起始行数
private int targetRowTo; //目标行数
private int targetColumnFrom; //起始列数
private int targetColumnTo; //目标列数
private String text; //单元格的值
private int rowLength; //占用总行数
private int colLength; //占用总列数
}
精简前辈的工具类(方面后面导入使用,有几个没用到我也放在这里的)
先引入工具类依赖（在pom中）
<properties>
<jxls.version>1.0.6</jxls.version>
<jxl.version>2.6.12</jxl.version>
</properties>
<!-- 添加jxls依赖 -->
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>${jxls.version}</version>
</dependency>
<!-- 添加jxl依赖 -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>${jxl.version}</version>
</dependency>
因为版本冲突，所以最后poi都是我改了之后的低版本poi(工具类都是注释默认高版本，引入低版本)
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;
import shade.org.apache.poi.hssf.usermodel.HSSFWorkbook;
import shade.org.apache.poi.ss.usermodel.*;
import shade.org.apache.poi.ss.util.CellRangeAddress;
import shade.org.apache.poi.xssf.usermodel.XSSFWorkbook;
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 Cell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case Cell.CELL_TYPE_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;
}
}
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.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;
import shade.org.apache.poi.hssf.usermodel.HSSFWorkbook;
import shade.org.apache.poi.ss.usermodel.*;
import shade.org.apache.poi.ss.util.CellRangeAddress;
import shade.org.apache.poi.xssf.usermodel.XSSFWorkbook;
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 Cell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case Cell.CELL_TYPE_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;
import shade.org.apache.poi.hssf.usermodel.HSSFWorkbook;
import shade.org.apache.poi.ss.usermodel.*;
import shade.org.apache.poi.ss.util.CellRangeAddress;
import shade.org.apache.poi.xssf.usermodel.XSSFWorkbook;
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 Cell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case Cell.CELL_TYPE_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（使用了,涉及sql没删除全）
package com.njry.utils.excel;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.njry.utils.DateHelper;
import com.njry.utils.StringUtils;
import jxl.Workbook;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.log4j.Logger;
import org.springframework.web.multipart.MultipartFile;
import shade.org.apache.poi.hssf.usermodel.*;
import shade.org.apache.poi.hssf.util.HSSFColor;
import shade.org.apache.poi.hssf.util.Region;
//import web.service.BaseService;
@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.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_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((short) align);
cellStyle.setVerticalAlignment((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(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_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");
System.out.println("BOLD:" + HSSFFont.BOLDWEIGHT_BOLD);
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)
font.setBoldweight(boldWeight);
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(valign);
if (align != -1)
cellStyle1.setAlignment(align);
return cellStyle1;
}
public void merge(HSSFSheet sheet, int row1, int col1, int row2, int col2) {
sheet.addMergedRegion(new Region(row1, (short) col1, row2, (short) col2));
}
public HSSFRow createRow(HSSFSheet sheet, int rowIndex) {
HSSFRow row = sheet.createRow(rowIndex);
return row;
}
/**
* eg: new HssfHelper().export(list, new String[][]{ {"用户号码", "MSISDN"},
* {"姓名", "NAME"}, {"投诉类型", "COMPLAIN_TYPE"}, {"工单流水号","TASKNO"},
* {"录音流水号","RECORDNO"}, {"投诉事由","COMPLAIN_CONTENT"}});
*
* @param list
* @param map
* @return
*/
public HSSFWorkbook export(List list, String[][] map, String sheetName) {
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = null;
for (int i = 0; i < map.length; i++) {
cell = hssfHelper.createCell(wb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
hssfHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
return wb;
}
public HSSFWorkbook export2(List list, String[][] map, String sheetName) {
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = null;
for (int i = 0; i < map.length; i++) {
cell = hssfHelper.createCell(wb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
if (map[j][2].equals("double")) {
if (!StringUtils.notEmpty(hash.get(map[j][1])).equals("")) {
hssfHelper.createCell(wb, row, j).setCellValue(
Double.valueOf(StringUtils
.notEmpty(hash.get(map[j][1]))));
}
} else {
hssfHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
}
return wb;
}
public HSSFWorkbook exportNumber(List list, String[][] map, String sheetName) {
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = null;
for (int i = 0; i < map.length; i++) {
cell = hssfHelper.createCell(wb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
try {
hssfHelper.createCell(wb, row, j).setCellValue(
Float.valueOf(StringUtils.notEmpty(hash.get(map[j][1]))));
} catch (Exception e) {
hssfHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
}
return wb;
}
public HSSFWorkbook exportByPageSize(List list, String[][] map, int pageSize) {
if (pageSize <= 0)
pageSize = 65530;
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
int currPage = 0;
int pages = list.size() / pageSize + 1;
for (int i = 0; i < list.size(); i++) {
if (i % pageSize == 0) {
int currPageSize = currPage == pages - 1 ? pageSize * currPage
+ list.size() % pageSize : pageSize * (currPage + 1);
String sheetName = (pageSize * currPage + 1) + "~"
+ currPageSize;
sheet = wb.createSheet(sheetName);
row = sheet.createRow((short) 0);
for (int j = 0; j < map.length; j++) {
cell = hssfHelper.createCell(wb, row, j);
cell.setCellStyle(headStyle);
cell.setCellValue(map[j][0]);
}
currPage++;
}
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
row = sheet.createRow((short) (i % pageSize) + 1);
hssfHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
return wb;
}
public String getCellStringValue(HSSFCell cell) {
DecimalFormat df = new DecimalFormat();
if (cell == null)
return "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case HSSFCell.CELL_TYPE_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 "";
}
}
public HSSFWorkbook export(List list, String sheet_name) {
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.createSheet(sheet_name);
HSSFRow row;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i);
Map map = (Map) list.get(i);
Object[] values = map.values().toArray();
for (int j = 0; j < values.length; j++) {
hssfHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(values[j]));
}
}
return wb;
}
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));
}
}
/**
* 通过给定的模板导出
* @param dataList
* @param filename 文件名
* @param dir_get 获取模板的路径
* @param dir_to 保存文件的路径
* @throws Exception
*/
public void exportBytemplet(List dataList,String filename,String dir_get,String dir_to) throws Exception {
// Map beans = new HashMap();
// beans.put("dataList", dataList);
// XLSTransformer transformer = new XLSTransformer();
// InputStream is = null;
// try {
// is = new BufferedInputStream(new FileInputStream(dir_get));
// HSSFWorkbook workbook = transformer.transformXLS(is, beans);
// FileOutputStream out=new FileOutputStream(dir_to+filename);
// workbook.write(out);
// is.close();
// out.flush();
// out.close();
// } catch (Exception e) {
// e.printStackTrace();
// }
}
public void export(List list, HSSFSheet sheet) {
HssfHelper hssfHelper = new HssfHelper();
HSSFRow row;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i);
Map map = (Map) list.get(i);
Object[] values = map.values().toArray();
for (int j = 0; j < values.length; j++) {
hssfHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(values[j]));
}
}
}
public HSSFCell createHeadCell(HSSFWorkbook wb, HSSFRow row, int column) {
HSSFFont headFont = wb.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = row.createCell((short)column);
cell.setCellStyle(headStyle);
return cell;
}
public void export2(HSSFWorkbook wb, List list, String[][] map, int headRows, int headCols, int sheetIndex) {
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
HSSFRow row = null;
HSSFCell cell = null;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + headRows);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
cell = hssfHelper.createCell(wb, row, j + headCols);
String val = StringUtils.notEmpty(hash.get(map[j][0]));
if(map[j][1].equals("double")){
if(!val.equals("")){
cell.setCellValue(Double.valueOf(val));
}
} else if(map[j][1].equals("date")) {//传入完整日期date类型字段,去除最后的.0后缀
if(!val.equals("")){
val = val.substring(0, val.length()-2);
System.out.println(val);
cell.setCellValue(val);
}
} else{
cell.setCellValue(val);
}
}
}
}
/**
* 校验Excel表头
* @param file 导入的文件
* @param titles 正确表头字符串数组
* @param sheet_index 表索引 从0开始
* @param row_index 行索引 从0开始
* @return
*/
@SuppressWarnings("deprecation")
public String checkExcelHead(MultipartFile file, String[] titles, int sheet_index, int row_index)
{
String message = "";
if (file == null || file.getSize() == 0)
{
message = "导入错误：文件不存在或文件大小为0!";
return message;
}
try
{
HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
HSSFSheet sheet = wb.getSheetAt(0);
if (sheet == null)
{
message = "导入错误：未找到sheet！";
}
else
{
HSSFRow row = wb.getSheetAt(sheet_index).getRow(row_index);
if (row == null)
{
message = "Excel标题列加载错误：没有标题列!";
return message;
}
int cols = row.getPhysicalNumberOfCells();
if (cols != titles.length)
{
message = "导入错误：导入的Excel必须为" + titles.length + "列！";
return message;
}
for (int i = 0; i < cols; i++)
{
HSSFCell cell = row.getCell((short) i);
String cellValue = this.getCellStringValue(cell);
if (!cellValue.equals(titles[i]))
{
message = "导入错误：Excel第" + this.getCellCode(i) + "列列名必须为“" + titles[i] + "”！";
break;
}
}
}
}
catch(Exception e)
{
message = "导入错误：文件读取异常!";
}
return message;
}
/**
* 创建临时表SQL
* @author 戴晓飞 2013-11-03
* @param tempTableName
* @param paramsStr
* @return
*/
public String getCreateSql(String tempTableName, String[] paramsStr, boolean orderFlag)
{
String createSql = "create table " + tempTableName + "(";
StringBuffer field = new StringBuffer(""); // 字段串
for(String param : paramsStr)
{
String[] temp = param.split(",");
field.append(temp[1] + " varchar(4000),");
}
// 拼接创建的SQL语句
if(orderFlag)
{
createSql = createSql + field.toString() + " decription varchar2(4000), show_order number)";
}
else
{
createSql = createSql + field.deleteCharAt(field.length() - 1) + " , decription varchar2(4000)) ";
}
logger.debug("插入临时表的SQL语句：" + createSql);
return createSql;
}
/**
* 插入临时表的SQL语句
* @author 戴晓飞 2013-11-03
* @param tempTableName
* @param paramsStr
* @return
*/
public String getInsertSql(String tempTableName, String[] paramsStr, boolean orderFlag)
{
String insertSql = "";
StringBuffer field = new StringBuffer(""); // 字段串
StringBuffer quesMark = new StringBuffer(""); // 占位符串
for(String param : paramsStr)
{
String[] temp = param.split(",");
field.append(temp[1] + ",");
quesMark.append("?,");
}
// 顺序标志为true，则字段串加show_order字段，占位符串增加一个占位符
if(orderFlag)
{
field.append("show_order");
quesMark.append("?");
}
else
{
field.deleteCharAt(field.length() - 1); // 去掉字段串末尾的逗号
quesMark.deleteCharAt(quesMark.length() - 1); // 去掉占位符串末尾的逗号
}
// 拼接插入的SQL语句
insertSql = "insert into " + tempTableName + "(" + field.toString() + ") values(" + quesMark.toString() + ")";
logger.debug("拼接插入的SQL语句：" + insertSql);
return insertSql;
}
public void export3(HSSFWorkbook wb, List list, String[][] map, int headRows, int headCols, int sheetIndex) {
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
HSSFRow row = null;
HSSFCell cell = null;
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + headRows);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
cell = hssfHelper.createCell(wb, row, j + headCols);
String val = StringUtils.notEmpty(hash.get(map[j][0]));
if(map[j][1].equals("double")){
if(!val.equals("")){
cell.setCellValue(Double.valueOf(val));
}
} else if(map[j][1].equals("date")) {//传入完整日期date类型字段,去除最后的.0后缀
if(!val.equals("")){
val = val.substring(0, val.length()-2);
cell.setCellValue(val);
}
} else{
cell.setCellValue(val);
}
cell.setCellStyle(cellStyle);
}
}
}
public void exportWithWatermark(HSSFWorkbook wb, List list, String[][] map, int headRows, int headCols, int sheetIndex)
{
HssfHelper hssfHelper = new HssfHelper();
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
HSSFRow row = null;
HSSFCell cell = null;
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + headRows);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
cell = hssfHelper.createCell(wb, row, j + headCols);
String val = StringUtils.notEmpty(hash.get(map[j][0]));
if(map[j][1].equals("double")){
if(!val.equals("")){
cell.setCellValue(Double.valueOf(val));
}
} else if(map[j][1].equals("date")) {//传入完整日期date类型字段,去除最后的.0后缀
if(!val.equals("")){
val = val.substring(0, val.length()-2);
cell.setCellValue(val);
}
} else{
cell.setCellValue(val);
}
cell.setCellStyle(cellStyle);
}
}
}
}
PoiHelper
package com.njry.utils.excel;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.njry.utils.DateHelper;
import com.njry.utils.StringUtils;
import net.sf.jxls.transformer.XLSTransformer;
import shade.org.apache.poi.hssf.usermodel.HSSFCellStyle;
import shade.org.apache.poi.hssf.usermodel.HSSFFont;
import shade.org.apache.poi.hssf.usermodel.HSSFWorkbook;
import shade.org.apache.poi.hssf.util.HSSFColor;
import shade.org.apache.poi.ss.usermodel.*;
import shade.org.apache.poi.ss.util.CellRangeAddress;
import shade.org.apache.poi.xssf.usermodel.XSSFWorkbook;
//import org.apache.poi.hssf.usermodel.HSSFCellStyle;
//import org.apache.poi.hssf.usermodel.HSSFFont;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.hssf.util.HSSFColor;
//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.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_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.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_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((short) align);
cellStyle.setVerticalAlignment((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(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_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");
System.out.println("BOLD:" + HSSFFont.BOLDWEIGHT_BOLD);
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.setBoldweight(boldWeight);
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(valign);
if (align != -1)
cellStyle1.setAlignment(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;
}
/**
* eg: new PoiHelper().export(list, new String[][]{ {"用户号码", "MSISDN"},
* {"姓名", "NAME"}}, "sheet名称", "2003");
* @param list 数据
* @param map 数据键值对
* @param sheetName sheet名称
* @param office_type office类型【2003 || 2007】
* @return
*/
public Workbook export(List list, String[][] map, String sheetName, String office_type) {
PoiHelper poiHelper = new PoiHelper(office_type);
Sheet sheet = wb.createSheet(sheetName);
Row row = sheet.createRow((short) 0);
Cell cell = null;
for (int i = 0; i < map.length; i++) {
cell = poiHelper.createCell(wb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
poiHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
return wb;
}
public Workbook export2(List list, String[][] map, String sheetName, String office_type) {
PoiHelper poiHelper = new PoiHelper(office_type);
Sheet sheet = wb.createSheet(sheetName);
Row row = sheet.createRow((short) 0);
Cell cell = null;
for (int i = 0; i < map.length; i++) {
cell = poiHelper.createCell(wb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
if (map[j][2].equals("double")) {
if (!StringUtils.notEmpty(hash.get(map[j][1])).equals("")) {
poiHelper.createCell(wb, row, j).setCellValue(
Double.valueOf(StringUtils
.notEmpty(hash.get(map[j][1]))));
}
} else {
poiHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
}
return wb;
}
public String getCellStringValue(Cell cell) {
DecimalFormat df = new DecimalFormat();
if (cell == null)
return "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case Cell.CELL_TYPE_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 Workbook export(List list, String sheet_name, String office_type) {
PoiHelper poiHelper = new PoiHelper(office_type);
Sheet sheet = wb.createSheet(sheet_name);
Row row;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i);
Map map = (Map) list.get(i);
Object[] values = map.values().toArray();
for (int j = 0; j < values.length; j++) {
poiHelper.createCell(wb, row, j).setCellValue(
StringUtils.notEmpty(values[j]));
}
}
return wb;
}
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));
}
}
/**
* 通过给定的模板导出
* @param dataList
* @param filename 文件名
* @param dir_get 获取模板的路径
* @param dir_to 保存文件的路径
* @throws Exception
*/
public void exportBytemplet(List dataList,String filename,String dir_get,String dir_to) throws Exception {
Map beans = new HashMap();
beans.put("dataList", dataList);
XLSTransformer transformer = new XLSTransformer();
InputStream is = null;
try {
is = new BufferedInputStream(new FileInputStream(dir_get));
Workbook workbook = (Workbook) transformer.transformXLS(is, beans);
FileOutputStream out=new FileOutputStream(dir_to+filename);
workbook.write(out);
is.close();
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
XssfHelper
package com.njry.utils.excel;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.njry.utils.DateHelper;
import com.njry.utils.StringUtils;
import net.sf.jxls.transformer.XLSTransformer;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.hssf.util.HSSFColor;
//import org.apache.poi.ss.usermodel.DateUtil;
//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;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import shade.org.apache.poi.hssf.usermodel.HSSFWorkbook;
import shade.org.apache.poi.hssf.util.HSSFColor;
import shade.org.apache.poi.ss.usermodel.DateUtil;
import shade.org.apache.poi.ss.util.CellRangeAddress;
import shade.org.apache.poi.xssf.usermodel.*;
@SuppressWarnings("unchecked")
public class XssfHelper {
public XSSFFont headFont;
public XSSFCellStyle headStyle;
public XSSFWorkbook xb;
public XssfHelper() {
xb = new XSSFWorkbook();
headFont = xb.createFont();
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// headFont.setColor(XSSFColor.BLACK.index);
headStyle = xb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(XSSFCellStyle.ALIGN_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((short) align);
cellStyle.setVerticalAlignment((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(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_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");
System.out.println("BOLD:" + XSSFFont.BOLDWEIGHT_BOLD);
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.setBoldweight(boldWeight);
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(valign);
if (align != -1)
cellStyle1.setAlignment(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;
}
/**
* eg: new HssfHelper().export(list, new String[][]{ {"用户号码", "MSISDN"},
* {"姓名", "NAME"}, {"投诉类型", "COMPLAIN_TYPE"}, {"工单流水号","TASKNO"},
* {"录音流水号","RECORDNO"}, {"投诉事由","COMPLAIN_CONTENT"}});
*
* @param list
* @param map
* @return
*/
public XSSFWorkbook export(List list, String[][] map, String sheetName) {
XssfHelper xssfHelper = new XssfHelper();
XSSFSheet sheet = xb.createSheet(sheetName);
XSSFRow row = sheet.createRow((short) 0);
XSSFCell cell = null;
for (int i = 0; i < map.length; i++) {
cell = xssfHelper.createCell(xb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
xssfHelper.createCell(xb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
return xb;
}
public XSSFWorkbook export2(List list, String[][] map, String sheetName) {
XssfHelper xssfHelper = new XssfHelper();
XSSFSheet sheet = xb.createSheet(sheetName);
XSSFRow row = sheet.createRow((short) 0);
XSSFCell cell = null;
for (int i = 0; i < map.length; i++) {
cell = xssfHelper.createCell(xb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
if (map[j][2].equals("double")) {
if (!StringUtils.notEmpty(hash.get(map[j][1])).equals("")) {
xssfHelper.createCell(xb, row, j).setCellValue(
Double.valueOf(StringUtils
.notEmpty(hash.get(map[j][1]))));
}
} else {
xssfHelper.createCell(xb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
}
return xb;
}
public XSSFWorkbook exportNumber(List list, String[][] map, String sheetName) {
XssfHelper xssfHelper = new XssfHelper();
XSSFSheet sheet = xb.createSheet(sheetName);
XSSFRow row = sheet.createRow((short) 0);
XSSFCell cell = null;
for (int i = 0; i < map.length; i++) {
cell = xssfHelper.createCell(xb, row, i);
cell.setCellStyle(headStyle);
cell.setCellValue(map[i][0]);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + 1);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
try {
xssfHelper.createCell(xb, row, j).setCellValue(
Float.valueOf(StringUtils.notEmpty(hash.get(map[j][1]))));
} catch (Exception e) {
xssfHelper.createCell(xb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
}
return xb;
}
public XSSFWorkbook exportByPageSize(List list, String[][] map, int pageSize) {
if (pageSize <= 0)
pageSize = 65530;
XssfHelper xssfHelper = new XssfHelper();
XSSFSheet sheet = null;
XSSFRow row = null;
XSSFCell cell = null;
int currPage = 0;
int pages = list.size() / pageSize + 1;
for (int i = 0; i < list.size(); i++) {
if (i % pageSize == 0) {
int currPageSize = currPage == pages - 1 ? pageSize * currPage
+ list.size() % pageSize : pageSize * (currPage + 1);
String sheetName = (pageSize * currPage + 1) + "~"
+ currPageSize;
sheet = xb.createSheet(sheetName);
row = sheet.createRow((short) 0);
for (int j = 0; j < map.length; j++) {
cell = xssfHelper.createCell(xb, row, j);
cell.setCellStyle(headStyle);
cell.setCellValue(map[j][0]);
}
currPage++;
}
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
row = sheet.createRow((short) (i % pageSize) + 1);
xssfHelper.createCell(xb, row, j).setCellValue(
StringUtils.notEmpty(hash.get(map[j][1])));
}
}
return xb;
}
public String getCellStringValue(XSSFCell cell) {
DecimalFormat df = new DecimalFormat();
if (cell == null)
return "";
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case XSSFCell.CELL_TYPE_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 XSSFWorkbook export(List list, String sheet_name) {
XssfHelper xssfHelper = new XssfHelper();
XSSFSheet sheet = xb.createSheet(sheet_name);
XSSFRow row;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i);
Map map = (Map) list.get(i);
Object[] values = map.values().toArray();
for (int j = 0; j < values.length; j++) {
xssfHelper.createCell(xb, row, j).setCellValue(
StringUtils.notEmpty(values[j]));
}
}
return xb;
}
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));
}
}
/**
* 通过给定的模板导出
* @param dataList
* @param filename 文件名
* @param dir_get 获取模板的路径
* @param dir_to 保存文件的路径
* @throws Exception
*/
public void exportBytemplet(List dataList,String filename,String dir_get,String dir_to) throws Exception {
Map beans = new HashMap();
beans.put("dataList", dataList);
XLSTransformer transformer = new XLSTransformer();
InputStream is = null;
try {
is = new BufferedInputStream(new FileInputStream(dir_get));
Workbook workbook = transformer.transformXLS(is, beans);
FileOutputStream out=new FileOutputStream(dir_to+filename);
workbook.write(out);
is.close();
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public XSSFCell createHeadCell(XSSFWorkbook xb, XSSFRow row, int column) {
XSSFFont headFont = xb.createFont();
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
XSSFCellStyle headStyle = xb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCell cell = row.createCell((short) column);
//cell.setEncoding((short) 1); // 支持中文导出
cell.setCellStyle(headStyle);
return cell;
}
public void export3(XSSFWorkbook xb, List list, String[][] map, int headRows, int headCols, int sheetIndex) {
XssfHelper xssfHelper = new XssfHelper();
XSSFSheet sheet = xb.getSheetAt(sheetIndex);
XSSFRow row = null;
XSSFCell cell = null;
XSSFCellStyle cellStyle = xb.createCellStyle();
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((short) i + headRows);
Map hash = (Map) list.get(i);
for (int j = 0; j < map.length; j++) {
cell = xssfHelper.createCell(xb, row, j + headCols);
String val = StringUtils.notEmpty(hash.get(map[j][0]));
if(map[j][1].equals("double")){
if(!val.equals("")){
cell.setCellValue(Double.valueOf(val));
}
} else if(map[j][1].equals("date")) {//传入完整日期date类型字段,去除最后的.0后缀
if(!val.equals("")){
val = val.substring(0, val.length()-2);
cell.setCellValue(val);
}
} else{
cell.setCellValue(val);
}
cell.setCellStyle(cellStyle);
}
}
}
}
接受的表格式
最后存入表的实体类
package com.njry.modules.system.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
import cn.hutool.core.bean.BeanUtil;
import io.swagger.annotations.ApiModelProperty;
import cn.hutool.core.bean.copier.CopyOptions;
import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
/**
* @description /
* @author wj
* @date 2024-05-09
**/
@Data
@TableName("SYS_TEST")
public class SysTest implements Serializable {
@TableId(value="excelid", type = IdType.INPUT)
@ApiModelProperty(value = "ID", hidden = true)
private Long id;
@ApiModelProperty(value = "excelid")
private Long excelid;
@ApiModelProperty(value = "parentlevel")
private String parentlevel;
@NotNull
@ApiModelProperty(value = "headid")
private String headid;
@NotNull
@ApiModelProperty(value = "colspan")
private Integer colspan;
@NotNull
@ApiModelProperty(value = "rowspan")
private Integer rowspan;
@ApiModelProperty(value = "width")
private Float width;
@NotNull
@ApiModelProperty(value = "rowfrom")
private Integer rowfrom;
@NotNull
@ApiModelProperty(value = "rowto")
private Integer rowto;
@NotNull
@ApiModelProperty(value = "colfrom")
private Integer colfrom;
@NotNull
@ApiModelProperty(value = "colto")
private Integer colto;
@NotBlank
@ApiModelProperty(value = "title")
private String title;
@ApiModelProperty(value = "子元素")
@TableField(exist = false)
private List<SysTest> children;
public void copy(SysTest source){
BeanUtil.copyProperties(source,this, CopyOptions.create().setIgnoreNullValue(true));
}
}
全局精华都在control里面(下面service，impl，mapper都不粘贴了)
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.Font;
//import org.apache.poi.ss.util.CellRangeAddress;
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 shade.org.apache.poi.hssf.usermodel.HSSFCell;
import shade.org.apache.poi.hssf.usermodel.HSSFRow;
import shade.org.apache.poi.hssf.usermodel.HSSFSheet;
import shade.org.apache.poi.hssf.usermodel.HSSFWorkbook;
import shade.org.apache.poi.ss.usermodel.CellStyle;
import shade.org.apache.poi.ss.usermodel.Font;
import shade.org.apache.poi.ss.util.CellRangeAddress;
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();
// 获取每一行有多少列 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);
headerCell.setBgcolor(colorHelper.getHex(headStyle.getFillForegroundColor()));
headerCell.setFontColor(colorHelper.getHex(headFont.getColor()));
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;
}
}
导入的模本类似如下图
效果
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
