首先看下工具類：
import java.awt.Color;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.swing.text.AttributeSet;
import javax.swing.text.Element;
import javax.swing.text.html.CSS;
import javax.swing.text.html.HTMLDocument;
import javax.swing.text.html.HTMLEditorKit;
import cn.vrview.dev.common.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
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.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.util.HtmlUtils;
import cn.vrview.dev.common.util.StringUtil;
public class ExcelTools {
/** log */
private static Logger log = LogManager.getLogger();
/**
* 導出excel
* <p>
* 使用方法：<br>
* <code> List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();<br>
* is = ExcelTools.exportXLS(dataList,new String[] {"createTime:日期","name:名稱", "sex:性别", "remark:備注"});
* </code>
*
* @param collect
* 待導出的資料集合
* @param header
* 要導出的列
* @return InputStream 傳回檔案流
*/
public static InputStream exportXLS(Collection<Map<String, Object>> collect, String[] header) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
HSSFWorkbook book = new HSSFWorkbook();
try {
// 添加一個sheet
HSSFSheet sheet = book.createSheet("Sheet1");
// 定義要導出的列名集合
Set<String> columns = new HashSet<String>();
// 設定單元格背景色
HSSFCellStyle cellStyle = book.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(new HSSFColor.YELLOW().getIndex());
// 生成表頭
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("序号");
// 列号從1開始
int n = 1;
// 解析頭字元串
for (String str : header) {
String[] arr = str.split(":");
columns.add(n + "," + arr[0]);// 添加要導出的字段名并且與列号n綁定
cell = row.createCell(n);
cell.setCellStyle(cellStyle);
cell.setCellValue(arr[1]);
n++;
}
// 生成資料行從1開開始，0為表頭
int i = 1;
// 生成資料行列
for (Map<String, Object> map : collect) {
HSSFRow dataRow = sheet.createRow(i);
// 生成序号
dataRow.createCell(0).setCellValue(i);
// 生成其他列
for (String column : columns) {
// 用逗号分割獲得字段名，[0]為列号用于和表頭标題對應上
String columnName = column.split(",")[1];
// 生成序号列
cell = dataRow.createCell(Integer.parseInt(column.split(",")[0]));
String value = "";
value = map.get(columnName) + "";
// 當value為null 時轉換為""
if ("null".equals(value)) {
value = "";
}
RichTextString richTextString = processHtml(book, value);
cell.getCellStyle().setWrapText(false);
cell.setCellValue(richTextString);
}
i++;
}
book.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return new ByteArrayInputStream(out.toByteArray());
}
/**
* 獲得excel檔案資料<br>
* 用法：<br>
* SheetInfo sheetInfo = new ExcelTools().new SheetInfo();<br>
* sheetInfo.setRowTitle(0); List<String> sheets = new ArrayList<String>();<br>
* String sheetName = "Sheet1"; sheets.add(sheetName);<br>
* sheetInfo.setSheetNames(sheets); <br>
* sheetInfo.setColumnsMapping(new String[] { "prodName:商品名稱",
* "prodSpec:規格", "collectPrice:價格:" + {@link RegExpEnum}
* RegExpEnum.NOTEMPTY_ISNUMBER, "priceUnit:機關", "collectMarket:報價市場",
* "prodLevel:等級" }); <br>
* Map<String, List> data = ExcelTools.getExcel(new File(path), sheetInfo);
*
* @param
*
* @param sheetInfo
* 初始化資訊
* @return Map {sheet1:List}
* @throws Exception
* Exception
*/
@SuppressWarnings("rawtypes")
public static Map getExcel(File f, SheetInfo sheetInfo, String excelType) throws Exception {
return getExcel(new FileInputStream(f), sheetInfo, excelType);
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public static Map getExcel(InputStream in, SheetInfo sheetInfo, String excelType) throws Exception {
Map<String, String> columnsMap = new HashMap<String, String>();
// 列驗證表達式map
List<String> errMsg = new ArrayList<String>();
int errNum = 0;// 錯誤總數
int errLimit = 10;// 限制錯誤提示數
/** 用于存儲Excel根據指定規則讀取的所有内容 */
Map excelInfo = new HashMap();
Workbook book = null;
try {
if (excelType.equals("xls")) {
book = new HSSFWorkbook(in);
//throw new BusinessException("excel版本太低，請使用2007以上版本（擴充名為：xlsx）");
} else {
book = new XSSFWorkbook(in);
}
} catch (OutOfMemoryError e) {
throw new RuntimeException("目前excel檔案過大，請檢查各個sheet表格中是否有無效空資料，包括帶有格式和線框的行列資料，請删除這些無效資料！正常excle檔案大小【1M】以内");
}
// checkTitle(book, sheetInfo);
// 獲得工作表數量
int sheetNum = sheetInfo.getSheetNames().size();
// 循環所有的工作表，讀取裡面的資料
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
// 獲得目前工作表對象
String sheetName = HtmlUtils.htmlUnescape(sheetInfo.getSheetNames().get(sheetIndex));
Map<String, String> validateMap = new HashMap<String, String>();
for (String mapstr : sheetInfo.getColumnsMapping().get(sheetName)) {
String[] arr = mapstr.split(":");
columnsMap.put(arr[1], arr[0]);
if (arr.length == 3) {// 若果驗證表達式不為空，則添加到map中
validateMap.put(arr[1], arr[2]);
}
}
Sheet sheet = book.getSheet(sheetName);
if (null == sheet) {
throw new RuntimeException(String.format("擷取表失敗，請确認Sheet《%s》是否存在于excel中", sheetName));
}
// 用于存儲所工作表中的資料内容
List sheetList = new ArrayList();
// 擷取目前表格的行數
int rows = sheet.getLastRowNum();
// 擷取目前表格的列數
int columns = sheet.getRow(sheetInfo.getRowTitle()).getLastCellNum();
if (rows <= sheetInfo.getRowTitle()) {// 如果目前表格沒有需要的資料就繼續下一次循環
continue;
}
// 獲得目前工作表标題内容
List<String> titleList = new ArrayList<String>();
// 循環每一行中的每一個單元格，讀取單元格内的值
Row titleRow = sheet.getRow(sheetInfo.getRowTitle());
for (int jj = 0; jj < columns; jj++) {
Cell cellTitle = titleRow.getCell(jj);
if (cellTitle != null) {
int row = cellTitle.getRowIndex();
int column = cellTitle.getColumnIndex();
if (isMergedRegion(sheet, row, column)) {
titleList.add(getMergedRegionValue(sheet, row, column));
} else {
titleList.add(getCellValue(cellTitle));
}
} else {
throw new RuntimeException("表頭讀取錯誤,目前設定為第" + (sheetInfo.getRowTitle() + 1) + "行<br/>表頭内容為：" + titleRow + "，請檢查是否正确，如有異常，可删除表頭格式，重新粘貼表頭！");
}
}
// System.out.println(titleList);
// 驗證表頭
String[] titles = sheetInfo.getColumnsMapping().get(sheetName);
for (String s : titles) {
String[] colArr = s.split(":");
// 如果Excel表格中的表頭缺少該字段
boolean include = false;
for (String t : titleList) {
if (StringUtils.deleteWhitespace(t).equalsIgnoreCase(colArr[1])) {
include = true;
break;
}
}
if (!include) {
throw new RuntimeException("【" + colArr[1] + "】'列不存在！目前Excel表頭：" + titleList);
}
}
// 開始循環每一行，讀取每一行的值,從标題下面一行開始讀取
for (int i = sheetInfo.getRowTitle() + 1; i <= rows; i++) {
Map rowMap = new HashMap();
Row dataRow = sheet.getRow(i);
if (dataRow == null) {
throw new RuntimeException(String.format("excel第[%d]行為空，請檢查!", i + 1));
}
for (int j = 0; j < columns; j++) {// 循環每一行中的每一個單元格，讀取單元格内的值
String columnTitle = titleList.get(j);
if ("".equals(columnTitle)) {
continue;
} else {
Cell cell = dataRow.getCell(j);
String value = "";
String columnMapping = "";
// 單元列對應的entity屬性名
for (String title : columnsMap.keySet()) {
if (StringUtils.deleteWhitespace(columnTitle).equalsIgnoreCase(title)) {
columnMapping = columnsMap.get(title);
break;
}
}
if (null != cell) {
cell.setCellType(Cell.CELL_TYPE_STRING);
CellStyle cellStyle= cell.getCellStyle();
//單元格背景顔色
if (excelType.equals("xls")) {
HSSFColor color=(HSSFColor) cellStyle.getFillForegroundColorColor();
if (j==0 && color!=null) {
rowMap.put("rowColor", convertRGBToHex(color.getTriplet()));
}
} else {
XSSFColor color=(XSSFColor) cellStyle.getFillForegroundColorColor();
if (j==0 && color!=null) {
rowMap.put("rowColor", color.getARGBHex().substring(2));
}
}
value = filterStr(cell + "");
int mergRow = getMergedRegionRow(sheet, cell);
if (mergRow > 0 && !StringUtil.isEmpty(value)) {
String rowspan="";
if (rowMap.get("rowspan")!=null) {
rowspan=rowMap.get("rowspan")+",";
}
rowMap.put("rowspan", rowspan+columnMapping+"-"+value+"-"+(mergRow + 1));
}
if ( cell.getCellComment()!=null) {
//System.out.println(columnMapping+"@comment:"+cell.getCellComment().getString());
rowMap.put(columnMapping+"@comment", cell.getCellComment().getString());
}
}
// String columnMapping = columnsMap.get(columnTitle);
String validateReg = "";
String validateRegMsg = "";
if (null != validateMap.get(columnTitle)) {
// 驗證正規表達式
RegExpEnum eum = RegExpEnum.valueOf(validateMap.get(columnTitle));
validateReg = eum.getValue();
validateRegMsg = eum.getText();
}
if (!StringUtil.isEmpty(validateReg)) {
if (value.matches(validateReg)) {
rowMap.put(columnMapping, value);
} else {
errNum++;
if (errNum <= errLimit) {
errMsg.add("第" + i + "行：【" + columnTitle + "】資料為:'" + value.trim() + "' 不比對！【" + validateRegMsg + "】</br>\n");
}
}
} else {
if (StringUtil.isEmpty(columnMapping)) {
continue;
} else {
//int row = cell.getRowIndex();
///int column = cell.getColumnIndex();
//if (isMergedRegion(sheet, row, column)) {
// rowMap.put(columnMapping, getMergedRegionValue(sheet, row, column));
//} else {
rowMap.put(columnMapping, value);
//}
}
}
}
}
sheetList.add(rowMap);
}
excelInfo.put(sheet.getSheetName(), sheetList);
}
in.close();
if (errMsg.size() > 0) {
if (errNum > errLimit) {
errMsg.add("您導入的資料模闆格式錯誤過多(共" + errNum + "個)，請仔細檢查模闆資料是否正确！");
}
throw new RuntimeException(errMsg.toString().replaceAll("\\[|\\]", ""));
}
// if (true) throw new RuntimeException("測試");
return excelInfo;
}
public static List<HashMap<String, String>> getExcel(InputStream in, SheetInfo sheetInfo) throws Exception {
Map<String, String> columnsMap = new HashMap<String, String>();
// 列驗證表達式map
Map<String, String> validateMap = new HashMap<String, String>();
List<String> errMsg = new ArrayList<String>();
int errNum = 0;// 錯誤總數
int errLimit = 10;// 限制錯誤提示數
for (String mapstr : sheetInfo.getColumnsMapping().get("columns")) {
String[] arr = mapstr.split(":");
columnsMap.put(arr[1], arr[0]);
if (arr.length == 3) {// 若果驗證表達式不為空，則添加到map中
validateMap.put(arr[1], arr[2]);
}
}
/** 用于存儲Excel根據指定規則讀取的所有内容 */
List excelInfo = new ArrayList();
Workbook book = WorkbookFactory.create(in);
// checkTitle(book, sheetInfo);
// 獲得工作表數量
int sheetNum = book.getNumberOfSheets();
// 循環所有的工作表，讀取裡面的資料
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
// 獲得目前工作表對象
Sheet sheet = book.getSheetAt(sheetIndex);
// 用于存儲所工作表中的資料内容
// List sheetList = new ArrayList();
// 擷取目前表格的行數
int rows = sheet.getLastRowNum();
// 擷取目前表格的列數
Row titleRow = sheet.getRow(sheetInfo.getRowTitle());
if (titleRow == null){
throw new BusinessException("檔案格式不正确，請重新選擇或者下載下傳模闆");
}
int columns = titleRow.getLastCellNum();
if (columns != sheetInfo.getColumnsMapping().get("columns").length){
throw new BusinessException("檔案格式不正确，請重新選擇或者下載下傳模闆");
}
if (rows <= sheetInfo.getRowTitle()) {// 如果目前表格沒有需要的資料就繼續下一次循環
throw new BusinessException("檔案格式不正确，請重新選擇或者下載下傳模闆");
}
// 獲得目前工作表标題内容
List<String> titleList = new ArrayList<String>();
// 循環每一行中的每一個單元格，讀取單元格内的值
for (int jj = 0; jj < columns; jj++) {
titleList.add(titleRow.getCell(jj).getStringCellValue());
}
// 驗證表頭
String[] titles = sheetInfo.getColumnsMapping().get("columns");
for (String s : titles) {
// 如果Excel表格中的表頭缺少該字段
if (!titleList.contains(s.split(":")[1])) {
// errMsg.add("該Excel表格的'" + sheet.getSheetName() + "'表的'" + s
// + "'列不存在！");
throw new BusinessException("檔案格式不正确，請重新選擇或者下載下傳模闆");
}
}
// 開始循環每一行，讀取每一行的值,從标題下面一行開始讀取
for (int i = sheetInfo.getRowTitle() + 1; i <= rows; i++) {
Map rowMap = new HashMap();
Row dataRow = sheet.getRow(i);
for (int j = 0; j < columns; j++) {// 循環每一行中的每一個單元格，讀取單元格内的值
String columnTitle = titleList.get(j);
if ("".equals(columnTitle)) {
continue;
} else {
Cell cell = dataRow.getCell(j);
String value = getCellValue(cell);
// 單元列對應的entity屬性名
String columnMapping = columnsMap.get(columnTitle);
String validateReg = "";
String validateRegMsg = "";
if (null != validateMap.get(columnTitle)) {
// 驗證正規表達式
RegExpEnum eum = RegExpEnum.valueOf(validateMap
.get(columnTitle));
validateReg = eum.getValue();
validateRegMsg = eum.getText();
}
if (!StringUtils.isEmpty(validateReg)) {
if (value.matches(validateReg)) {
rowMap.put(columnMapping, value);
} else {
errNum++;
if (errNum <= errLimit) {
errMsg.add("第" + i + "行：【" + columnTitle
+ "】資料為:'" + value.trim()
+ "' 不比對！【" + validateRegMsg
+ "】</br>\n");
}
}
} else {
rowMap.put(columnMapping, value);
}
}
}
excelInfo.add(rowMap);
}
// excelInfo.put(sheet.getSheetName(), sheetList);
}
in.close();
if (errMsg.size() > 0) {
// if (errNum > errLimit) {
// errMsg.add("您導入的資料模闆格式錯誤過多(共" + errNum + "個)，請仔細檢查模闆資料是否正确！");
// }
throw new RuntimeException(errMsg.toString().replaceAll("\\[|\\]",
""));
}
return excelInfo;
}
/**
*
* 用于excel操作，表格初始化資訊
*
*/
public class SheetInfo {
/** 标題所在的行，起始行是0，不是1 */
private int rowTitle = 1;
/** 需要讀取資料字段中文名對應的entity屬性名 */
private Map<String, String[]> columnsMapping;
/** 需要讀取資料的sheet的名字 */
public List<String> sheetNames = new ArrayList<String>();
public SheetInfo(List<String> sheetNames) {
// 假如沒有定義sheetNames，則給予其預設值”Sheet1“
if (null == sheetNames || sheetNames.size() == 0) {
this.sheetNames.add("Sheet1");
} else {
this.sheetNames = sheetNames;
}
}
public SheetInfo() {
// 假如沒有定義sheetNames，則給予其預設值”Sheet1“
if (null == sheetNames || sheetNames.size() == 0) {
sheetNames.add("Sheet1");
}
}
public int getRowTitle() {
return rowTitle;
}
public void setRowTitle(int rowTitle) {
this.rowTitle = rowTitle;
}
public Map<String, String[]> getColumnsMapping() {
return columnsMapping;
}
public void setColumnsMapping(Map<String, String[]> columnsMapping) {
this.columnsMapping = columnsMapping;
}
public List<String> getSheetNames() {
return sheetNames;
}
public void setSheetNames(List<String> sheetNames) {
this.sheetNames = sheetNames;
}
}
/**
*
* 内部枚舉類
*
*/
public enum RegExpEnum {
/** 不為空 */
NOTEMPTY("不能為空", "(?! +$).+"),
/** 必須為數字 */
ISNUMBER("必須為數字", "\\d*"),
/** 不為空并且為數字 */
NOTEMPTY_ISNUMBER("不能為空且必須為數字", "\\d+");
/** text */
private String text;
/** level */
private String value;
public String getText() {
return text;
}
public String getValue() {
return value;
}
private RegExpEnum(String text, String value) {
this.text = text;
this.value = value;
}
}
/**
* 将html轉為 RichTextString
*
* @param wb
* HSSFWorkbook
* @param html
* html
* @return RichTextString
*/
@SuppressWarnings("unused")
private static RichTextString processHtml(HSSFWorkbook wb, String html) {
RichTextString rt = null;
HTMLEditorKit kit = new HTMLEditorKit();
HTMLDocument doc = (HTMLDocument) kit.createDefaultDocument();
try {
kit.insertHTML(doc, doc.getLength(), html, 0, 0, null);
StringBuffer sb = new StringBuffer();
for (int lines = 0, lastPos = -1; lastPos < doc.getLength(); lines++) {
// if (lines > 0) {
// sb.append('\n');
// }
Element line = doc.getParagraphElement(lastPos + 1);
lastPos = line.getEndOffset();
for (int elIdx = 0; elIdx < line.getElementCount(); elIdx++) {
final Element frag = line.getElement(elIdx);
String subtext = doc.getText(frag.getStartOffset(), frag.getEndOffset() - frag.getStartOffset());
if (!subtext.equals("\n")) {
sb.append(subtext);
}
}
}
CreationHelper ch = wb.getCreationHelper();
rt = ch.createRichTextString(sb.toString());
for (int lines = 0, lastPos = -1; lastPos < doc.getLength(); lines++) {
Element line = doc.getParagraphElement(lastPos + 1);
lastPos = line.getEndOffset();
for (int elIdx = 0; elIdx < line.getElementCount(); elIdx++) {
final Element frag = line.getElement(elIdx);
Font font = getFontFromFragment(wb, frag);
rt.applyFont(frag.getStartOffset() + lines, frag.getEndOffset() + lines, font);
}
}
} catch (Exception e) {
log.warn(e.getMessage());
// e.printStackTrace();
}
return rt;
}
/**
* 擷取字型
*
* @param wb
* Workbook
* @param frag
* frag
* @return Font
* @throws Exception
* Exception
*/
private static Font getFontFromFragment(Workbook wb, Element frag) throws Exception {
Font font = wb.createFont();
final AttributeSet as = frag.getAttributes();
final Enumeration<?> ae = as.getAttributeNames();
while (ae.hasMoreElements()) {
final Object attrib = ae.nextElement();
if (CSS.Attribute.COLOR.equals(attrib)) {
Field f = as.getAttribute(attrib).getClass().getDeclaredField("c");
f.setAccessible(true);
Color c = (Color) f.get(as.getAttribute(attrib));
if (font instanceof XSSFFont) {
((XSSFFont) font).setColor(new XSSFColor(c));
} else if (font instanceof HSSFFont && wb instanceof HSSFWorkbook) {
HSSFPalette pal = ((HSSFWorkbook) wb).getCustomPalette();
HSSFColor col = pal.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue());
((HSSFFont) font).setColor(col.getIndex());
}
} else if (CSS.Attribute.FONT_WEIGHT.equals(attrib)) {
if ("bold".equals(as.getAttribute(attrib).toString())) {
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
}
}
return font;
}
public static int getMergedRegionRow(Sheet sheet, Cell cell) {
// 得到一個sheet中有多少個合并單元格
int sheetmergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetmergerCount; i++) {
// 得出具體的合并單元格
CellRangeAddress ca = sheet.getMergedRegion(i);
// 得到合并單元格的起始行, 結束行, 起始列, 結束列
int firstC = ca.getFirstColumn();
int lastC = ca.getLastColumn();
int firstR = ca.getFirstRow();
int lastR = ca.getLastRow();
// 判斷該單元格是否在合并單元格範圍之内, 如果是, 則傳回 true
if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
if (cell.getRowIndex() == firstR) {
return lastR - firstR;
}
}
}
return 0;
}
/**
* 擷取合并單元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
/**
* 判斷指定的單元格是否是合并單元格
*
* @param sheet
* @param row
* 行下标
* @param column
* 列下标
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 判斷sheet頁中是否含有合并單元格
*
* @param sheet
* @return
*/
@SuppressWarnings("unused")
private boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
}
/**
* 合并單元格
*
* @param sheet
* @param firstRow
* 開始行
* @param lastRow
* 結束行
* @param firstCol
* 開始列
* @param lastCol
* 結束列
*/
@SuppressWarnings("unused")
private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
/**
* 擷取單元格的值
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null)
return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 處理日期格式、時間格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = cell.getDateCellValue();
return String.valueOf(sdf.format(date));
} else if (cell.getCellStyle().getDataFormat() == 31) {
// 處理自定義日期格式：yy年mm月dd日(通過判斷單元格的格式id解決，id的值是31)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
return String.valueOf(sdf.format(date));
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
return String.valueOf(format.format(value));
}
}
return "";
}
public static String filterStr(String str) {
str = str.replace(String.valueOf((char) 160), "").replace(String.valueOf((char) 65279), "");
str = str.trim();
return str;
}
public static void main(String[] args) {
System.out.println(convertRGBToHex(HSSFColor.YELLOW.triplet));
System.out.println(new XSSFColor(Color.YELLOW).getARGBHex().substring(2));
System.err.println(HtmlUtils.htmlUnescape("彙總（電視&盒子&路由器）"));
}
static String convertRGBToHex(short[] rgb) {
int r= rgb[0],g=rgb[1],b=rgb[2];
String rFString, rSString, gFString, gSString, bFString, bSString, result;
int red, green, blue;
int rred, rgreen, rblue;
red = r / 16;
rred = r % 16;
if (red == 10) rFString = "A";
else if (red == 11) rFString = "B";
else if (red == 12) rFString = "C";
else if (red == 13) rFString = "D";
else if (red == 14) rFString = "E";
else if (red == 15) rFString = "F";
else rFString = String.valueOf(red);
if (rred == 10) rSString = "A";
else if (rred == 11) rSString = "B";
else if (rred == 12) rSString = "C";
else if (rred == 13) rSString = "D";
else if (rred == 14) rSString = "E";
else if (rred == 15) rSString = "F";
else rSString = String.valueOf(rred);
rFString = rFString + rSString;
green = g / 16;
rgreen = g % 16;
if (green == 10) gFString = "A";
else if (green == 11) gFString = "B";
else if (green == 12) gFString = "C";
else if (green == 13) gFString = "D";
else if (green == 14) gFString = "E";
else if (green == 15) gFString = "F";
else gFString = String.valueOf(green);
if (rgreen == 10) gSString = "A";
else if (rgreen == 11) gSString = "B";
else if (rgreen == 12) gSString = "C";
else if (rgreen == 13) gSString = "D";
else if (rgreen == 14) gSString = "E";
else if (rgreen == 15) gSString = "F";
else gSString = String.valueOf(rgreen);
gFString = gFString + gSString;
blue = b / 16;
rblue = b % 16;
if (blue == 10) bFString = "A";
else if (blue == 11) bFString = "B";
else if (blue == 12) bFString = "C";
else if (blue == 13) bFString = "D";
else if (blue == 14) bFString = "E";
else if (blue == 15) bFString = "F";
else bFString = String.valueOf(blue);
if (rblue == 10) bSString = "A";
else if (rblue == 11) bSString = "B";
else if (rblue == 12) bSString = "C";
else if (rblue == 13) bSString = "D";
else if (rblue == 14) bSString = "E";
else if (rblue == 15) bSString = "F";
else bSString = String.valueOf(rblue);
bFString = bFString + bSString;
result = rFString + gFString + bFString;
return result;
}
}
再看下from.jsp頁面
<body>
<div>
<form id="mainform" action="${ctx}/bom/ciscaseaction/${action}" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<a href="${ctx}/static/案由導入模闆.xls" target="_blank" rel="external nofollow" >下載下傳模闆</a>
</form>
</div>
<script type="text/javascript">
$(function(){
$('#mainform').form({
onSubmit: function(){
var isValid = $(this).form('validate');
return isValid; // 傳回false終止表單送出
},
success:function(data){
successTip(data,dg,d);
}
});
});
</script>
</body>
主界面jsp
<a href="javascript(0)" target="_blank" rel="external nofollow" class="easyui-linkbutton" plain="true" iconCls="icon-standard-application-go" οnclick="importAction()">導入</a>
//導入
function importAction(){
d=$("#dlg").dialog({
title: '案由導入',
width: 500,
height: 500,
href:'${ctx}/bom/ciscaseaction/importAction/',
maximizable:true,
modal:true,
buttons:[{
text:'導入',
handler:function(){
$('#mainform').submit();
}
},{
text:'取消',
handler:function(){
d.panel('close');
}
}]
});
}
頁面點選的效果是，點選導入會跳入from.jsp頁面
再看controller層
/**
* 導入頁面
*/
@RequestMapping(value = "importAction", method = RequestMethod.GET)
public String importForm( Model model) {
model.addAttribute("action", "import");
return "system/cisCaseActionImoportForm";
}
/**
* 導入
*/
@RequestMapping(value = "import", method = RequestMethod.POST)
@ResponseBody
public String importForm(@RequestParam("file") MultipartFile multipartFile, Model model) throws Exception {
cisCaseActionService.upload(multipartFile);
return "success";
}
service層
/**
* 導入案由
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public void upload(MultipartFile multipartFile) throws Exception {
InputStream inputStream = multipartFile.getInputStream();
ExcelTools excelTools = new ExcelTools();
ExcelTools.SheetInfo sheetInfo = excelTools.new SheetInfo();
sheetInfo.setRowTitle(0);
Map columns = new HashMap();
columns.put("columns",new String[]{"name:案由名稱", "violateLaw:違反法律", "punishBasis:處罰依據"});
sheetInfo.setColumnsMapping(columns);
List<HashMap<String, String>> mapList = ExcelTools.getExcel(inputStream, sheetInfo);
for (int i = 0; i < mapList.size(); i++){
HashMap<String, String> map = mapList.get(i);
String name = map.get("name");
if (StringUtils.isEmpty(name)){
throw new BusinessException("第" + (i+2) + "案由名稱不能為空");
}
String violateLaw = map.get("violateLaw");
String punishBasis = map.get("punishBasis");
CisCaseAction cisCaseAction=new CisCaseAction();
cisCaseAction.setName(name);
cisCaseAction.setViolateLaw(violateLaw);
cisCaseAction.setPunishBasis(punishBasis);
this.insert(cisCaseAction); //調用同一層的插入方法
}
}
![Java POI完成Excel導入導出示例一、導出 二、導入（資料庫）三、最後[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)