Java中excel表数据的批量导入方法

Java中excel表数据的批量导入方法

本文实例为大家分享了java中excel表数据的批量导入，供大家参考，具体内容如下

首先看下工具类：

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

*

* 使用方法：

* List

* is = ExcelTools.exportXLS(dataList,new String[] {"createTime:日期","name:名称", "sex:性别", "remark:备注"});

*

*

* @param collect

* 待导出的数据集合

* @param header

* 要导出的列

* @return InputStream 返回文件流

*/

public static InputStream exportXLS(Collection

ByteArrayOutputStream out = new ByteArrayOutputStream();

HSSFWorkbook book = new HSSFWorkbook();

try {

// 添加一个sheet

HSSFSheet sheet = book.createSheet("Sheet1");

// 定义要导出的列名集合

Set columns = new HashSet();

// 设置单元格背景色

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 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文件数据

* 用法：

* SheetInfo sheetInfo = new ExcelTools().new SheetInfo();

* sheetInfo.setRowTitle(0); List sheets = new ArrayList();

* String sheetName = "Sheet1"; sheets.add(sheetName);

* sheetInfo.setSheetNames(sheets);

* sheetInfo.setColumnsMapping(new String[] { "prodName:商品名称",

* "prodSpec:规格", "collectPrice:价格:" + {@link RegExpEnum}

* RegExpEnum.NOTEMPTY_ISNUMBER, "priceUnit:单位", "collectMarket:报价市场",

* "prodLevel:等级" });

* Map 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 columnsMap = new HashMap();

// 列验证表达式map

List errMsg = new ArrayList();

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 validateMap = new HashMap();

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 titleList = new ArrayList();

// 循环每一行中的每一个单元格，读取单元格内的值

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) + "行
表头内容为：" + 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 + "】\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> getExcel(InputStream in, SheetInfo sheetInfo) throws Exception {

Map columnsMap = new HashMap();

// 列验证表达式map

Map validateMap = new HashMap();

List errMsg = new ArrayList();

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 titleList = new ArrayList();

// 循环每一行中的每一个单元格，读取单元格内的值

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

+ "】\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操作，表格初始化信息

*

* @author: 季乐

* @date: 2013-12-2 下午1:43:04

* @since: 1.0

*/

public class SheetInfo {

/** 标题所在的行，起始行是0，不是1 */

private int rowTitle = 1;

/** 需要读取数据字段中文名对应的entity属性名 */

private Map columnsMapping;

/** 需要读取数据的sheet的名字 */

public List sheetNames = new ArrayList();

public SheetInfo(List 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 getColumnsMapping() {

return columnsMapping;

}

public void setColumnsMapping(Map columnsMapping) {

this.columnsMapping = columnsMapping;

}

public List getSheetNames() {

return sheetNames;

}

public void setSheetNames(List sheetNames) {

this.sheetNames = sheetNames;

}

}

/**

*

* 内部枚举类

*

* @author: 季乐

* @date: 2013-12-2 下午1:43:24

* @since: 1.0

*/

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页面

下载模板

主界面jsp

复制代码 代码如下:

导入

//导入

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> mapList = ExcelTools.getExcel(inputStream, sheetInfo);

for (int i = 0; i < mapList.size(); i++){

HashMap 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); //调用同一层的插入方法

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。