1.pom依赖
org.apache.poi
poi
3.14
org.apache.poi
poi-ooxml-schemas
3.14
org.apache.poi
poi-ooxml
3.14
2.CommonUtil工具类(为方便运算)
public class CommonUtil {
public static boolean isEmpty(String s) {
return s == null || s.equals("");
}
public static boolean isEmpty(Object obj) {
if (obj == null) {
return true;
} else if (obj instanceof String) {
String instance = (String) obj;
if (instance.trim().length() <= 0 || "null".equalsIgnoreCase(instance)) {
return true;
}
} else if (obj instanceof Integer) {
Integer instance = (Integer) obj;
if (instance < 0) {
return true;
}
} else if (obj instanceof List) {
List instance = (List) obj;
if (instance.size() <= 0) {
return true;
}
} else if (obj instanceof Map) {
Map instance = (Map) obj;
if (instance.size() <= 0) {
return true;
}
} else if (obj instanceof Object[]) {
Object[] instance = (Object[]) obj;
if (instance.length <= 0) {
return true;
}
} else if (obj instanceof Long) {
Long instance = (Long) obj;
if (instance < 0) {
return true;
}
}
return false;
}
public static boolean notEmpty(Object obj) {
return !isEmpty(obj);
}
public static boolean exactEqual(String source, String target) {
if (source == null || target == null) {
return false;
}
if (source.length() != target.length()) {
return false;
}
char[] sc = source.toCharArray();
char[] tc = target.toCharArray();
for (int i = 0; i < sc.length; i++) {
if (sc[i] == tc[i]) {
continue;
} else {
return false;
}
}
return true;
}
public static boolean isNumber(String str) {
if (isEmpty(str)) {
return false;
}
Pattern pattern = Pattern.compile("[0-9]*");
if (str.indexOf(".") > 0) {// 判断是否有小数点
if (str.indexOf(".") == str.lastIndexOf(".") && str.split("\.").length == 2) { // 判断是否只有一个小数点
return pattern.matcher(str.replace(".", "")).matches();
} else {
return false;
}
} else {
return pattern.matcher(str).matches();
}
}
public static boolean isInteger(String str) {
if (null == str || "".equals(str)) {
return false;
}
Pattern pattern = Pattern.compile("^[-\+]?[\d]*$");
return pattern.matcher(str).matches();
}
public static boolean isDouble(String str) {
if (null == str || "".equals(str)) {
return false;
}
Pattern pattern = Pattern.compile("^[-\+]?\d*[.]\d+$"); // 之前这里正则表达式错误，现更正
return pattern.matcher(str).matches();
}
public static boolean isDate(String date) {
Pattern p = Pattern.compile("^((\d{2}(([02468][048])|([13579][26]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|([1-2][0-9])))))|(\d{2}(([02468][1235679])|([13579][01345789]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))?$");
return p.matcher(date).matches();
}
public static Date parseDate(String string) {
if (notEmpty(string)) {
try {
return new SimpleDateFormat("yyyy-MM-dd").parse(string);
} catch (ParseException e) {
}
}
return null;
}
public static String join(String separator, List strList) {
StringBuilder sb = new StringBuilder();
for (String string : strList) {
sb.append(separator + string);
}
String saveStr = sb.toString();
return saveStr.length() > 0 ? saveStr.substring(separator.length()) : "";
}
public static boolean isOutLength(String s, int l) {
if (null == s || 0 == s.length()) {
return false;
}
if (s.length() > l) {
return true;
}
return false;
}
public static String extractText(String htmlStr) {
// 定义script的正则表达式{或]*?>[\s\S]*?<\/script> // }
String regExscript = "<[\s]*?script[^>]*?>[\s\S]*?<[\s]*?\/[\s]*?script[\s]*?>";
// 定义style的正则表达式{或]*?>[\s\S]*?<\/style>// }
String regExStyle = "<[\s]*?style[^>]*?>[\s\S]*?<[\s]*?\/[\s]*?style[\s]*?>";
// 定义HTML标签的正则表达式
String regExHtml = "<[^>]+>";
Pattern pscript = Pattern.compile(regExscript, Pattern.CASE_INSENSITIVE);
Matcher mscript = pscript.matcher(htmlStr);
// 过滤script标签
htmlStr = mscript.replaceAll("");
Pattern pStyle = Pattern.compile(regExStyle, Pattern.CASE_INSENSITIVE);
Matcher mStyle = pStyle.matcher(htmlStr);
// 过滤style标签
htmlStr = mStyle.replaceAll("");
Pattern pHtml = Pattern.compile(regExHtml, Pattern.CASE_INSENSITIVE);
Matcher mHtml = pHtml.matcher(htmlStr);
// 过滤html标签
htmlStr = mHtml.replaceAll("");
return htmlStr;
}
}
3.ExcelWriteUtil (EXCEL导出工具类)
public class ExcelWriteUtil {
public static XSSFWorkbook dropDownList2007(Workbook wb, int sheetnum, String[] datas, int startRow, int endRow, int startCol, int endCol) throws Exception {
XSSFWorkbook workbook = (XSSFWorkbook) wb;
dropDownList2007(workbook.getSheetAt(sheetnum), datas, startRow, endRow, startCol, endCol);
return workbook;
}
public static XSSFSheet dropDownList2007(XSSFSheet realSheet, String[] datas, int startRow, int endRow, int startCol, int endCol) {
if (datas.length > 0) {
String[] array = valiDatas(datas);
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(realSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(array);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
addressList = new CellRangeAddressList(startRow, endRow, startCol, endCol);
validation = (XSSFDataValidation) dvHelper.createvalidation(dvConstraint, addressList);
realSheet.addValidationData(validation);
}
return realSheet;
}
private static String[] valiDatas(String[] datas) {
// 先去掉重复数据
List list = new ArrayList();
for (String string : datas) {
if (list.contains(string)) {
continue;
}
list.add(string);
}
String temp = "";
for (String string : list) {
temp += "," + string;// 用逗号拼接数据
}
if (temp.startsWith(",")) {
temp = temp.substring(1);// 去掉开头的逗号
}
// 在截取总长超过255的部分
int maxLength = 255;
if (temp.length() > maxLength) {
String end = temp.substring(maxLength);
temp = temp.substring(0, maxLength);
if (!end.startsWith(",")) {// 如果截断的部分不是某个逗号后面的部分
temp = temp.substring(0, temp.lastIndexOf(","));// 舍弃左后一个逗号后面的部分
}
}
String[] result = temp.split(",");
return result;
}
public static XSSFWorkbook createTemplate2007(String sheetName, int titleRow, List titleList) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
if (CommonUtil.isEmpty(sheetName)) {
sheetName = "模板";
}
createSheet(workbook, sheetName, titleRow, titleList);
return workbook;
}
public static XSSFSheet createSheet(XSSFWorkbook workbook, String sheetName, int titleRow, List titleList) {
XSSFSheet sheet = workbook.createSheet(sheetName);
XSSFRow row = sheet.createRow(titleRow);
CellStyle cellStyle = createCellStyle(workbook, "宋体", 16, IndexedColors.BLACK.getIndex(), true, false, false, false, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
cellStyle = setCellBackgroundStyle(cellStyle, HSSFColor.WHITE.index, CellStyle.BORDER_THIN, true, true, true, true);
int cellIndex = 0;
for (String title : titleList) {
createCell(row, cellIndex, title, cellStyle);
// 标题单元格宽度自适应
sheet.autoSizeColumn((short) cellIndex);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(cellIndex, sheet.getColumnWidth(cellIndex) * 17 / 10);
cellIndex++;
}
return sheet;
}
public static Row createRow(XSSFWorkbook workbook, int sheetNum, int rowNum) {
return workbook.getSheetAt(sheetNum).createRow(rowNum);
}
public static void createExcelRow(XSSFWorkbook workbook, XSSFSheet sheet, int titleRow, List titleList) {
XSSFRow row = sheet.createRow(titleRow);
int cellIndex = 0;
for (String title : titleList) {
createCell(row, cellIndex, title, null);
cellIndex++;
}
}
public static Cell createCell(Row row, int cellIndex, String cellContent, CellStyle cellStyle) {
Cell cell = row.createCell(cellIndex);
cell.setCellValue(cellContent);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
return cell;
}
public static CellStyle createCellStyle(Workbook wb, String fontName, int fontSize, int colorIndex, boolean isBold, boolean isItalic, boolean isStrike, boolean isUnderline, int underLineStyle, int hAlign, int vAlign) {
// 设置字体
Font font = wb.createFont();
// 字体名
font.setFontName(fontName);
// 字大小
font.setFontHeightInPoints((short) fontSize);
// 字体颜色
if (colorIndex >= 0) {
font.setColor((short) colorIndex);
}
// 粗体
if (isBold) {
font.setBold(true);
}
// 倾斜
if (isItalic) {
font.setItalic(true);
}
// 横线传体
if (isStrike) {
font.setStrikeout(true);
}
// 下划线
if (isUnderline) {
font.setUnderline((byte) underLineStyle);
}
CellStyle cellStyle = wb.createCellStyle();
// 换行
cellStyle.setWrapText(true);
cellStyle.setFont(font);
// 水平对齐
cellStyle.setAlignment((short) hAlign);
// 垂直对齐
cellStyle.setVerticalAlignment((short) vAlign);
return cellStyle;
}
public static CellStyle setCellBackgroundStyle(CellStyle cellStyle, short color, short style, boolean isBottom, boolean isLeft, boolean isTop, boolean isRight) {
cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index);
if (isBottom) {
// 下边框
cellStyle.setBorderBottom(style);
}
if (isBottom) {
// 左边框
cellStyle.setBorderLeft(style);
}
if (isBottom) {
// 上边框
cellStyle.setBorderTop(style);
}
if (isBottom) {
// 右边框
cellStyle.setBorderRight(style);
}
return cellStyle;
}
public static void setColumnFormat(XSSFWorkbook wb, String sheetName, CellStyle css, int columnNum, String format) {
DataFormat dataFormat = wb.createDataFormat();
if (css == null) {
css = wb.createCellStyle();
}
css.setDataFormat(dataFormat.getFormat(format));
XSSFSheet sheet = wb.getSheet(sheetName);
sheet.setDefaultColumnStyle(columnNum, css);
}
public static XSSFWorkbook createTimetableSheet(String sheetName) {
if (CommonUtil.isEmpty(sheetName)) {
sheetName = "模板";
}
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.createSheet(sheetName);
return workbook;
}
public static void addCellComment(XSSFWorkbook workbook, Sheet sheet, int rowIndex, int cellIndex, String commentStr) {
XSSFDrawing draw = (XSSFDrawing) sheet.createDrawingPatriarch();
// 定义注释的大小和位置
XSSFComment comment = draw.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 2, 9, 7));
XSSFRichTextString rtf = new XSSFRichTextString(commentStr);
XSSFFont commentFormatter = workbook.createFont();
commentFormatter.setFontName("宋体");
// 设置字体大小
commentFormatter.setFontHeightInPoints((short) 9);
rtf.applyFont(commentFormatter);
comment.setString(rtf);
// 将批注添加到单元格对象中
sheet.getRow(rowIndex).getCell(cellIndex).setCellComment(comment);
}
public static void createExampleRow(XSSFWorkbook workbook, String sheetName, List example, int rowIndex) {
XSSFSheet sheet = workbook.getSheet(sheetName);
ExcelWriteUtil.createExcelRow(workbook, sheet, rowIndex, example);
// 示例行背景色
XSSFRow row = sheet.getRow(rowIndex);
CellStyle style = ExcelWriteUtil.createCellStyle(workbook, "宋体", 12, IndexedColors.BLACK.getIndex(), false, false, false, false, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
for (int i = 0; i < example.size(); i++) {
row.getCell(i).setCellStyle(style);
}
ExcelWriteUtil.addCellComment(workbook, sheet, rowIndex, 0, "示例行,不要删除.");
}
public static CellStyle setBorderStyle(CellStyle style, short top, short right, short bottom, short left) {
style.setBorderTop(top);
style.setBorderRight(right);
style.setBorderBottom(bottom);
style.setBorderLeft(left);
return style;
}
public static String objToString(Object obj) {
return CommonUtil.isEmpty(obj) ? "" : obj.toString();
}
}
4.ExcelReadUtil (EXCEL读取工具类)
public class ExcelReadUtil {
public static Workbook getWorkbook(MultipartFile file) {
String name = file.getOriginalFilename();
try {
if (isExcel2007(name)) {
return new XSSFWorkbook(OPCPackage.open(file.getInputStream()));
} else {
return new HSSFWorkbook(file.getInputStream());
}
} catch (IOException ioe) {
ioe.printStackTrace();
} catch (InvalidFormatException ife) {
ife.printStackTrace();
}
return null;
}
public static boolean valueTemplate(Workbook workbook, int sheetIndex, int rowIndex, String[] titles) {
List titleList = new ArrayList<>();
Row row = workbook.getSheetAt(sheetIndex).getRow(rowIndex);
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
String cellValue = "";
if (null != cell) {
cellValue = parseExcel(cell).trim();
}
titleList.add(cellValue);
}
if (titleList.size() != titles.length) {
return false;
}
for (int i = 0; i < titles.length; i++) {
if (!titles[i].equals(titleList.get(i))) {
return false;
}
}
return true;
}
public static List> readData(Workbook workbook, int sheetIndex, int startRow, int startCol, String[] colKeys) {
List> dataList = new ArrayList<>();
int size = 0;
if (colKeys != null) {
size = colKeys.length;
}
// sheetIndex 从0开始
Sheet sheet = workbook.getSheetAt(sheetIndex);
// 行循环开始
for (int i = startRow; i < sheet.getLastRowNum() + 1; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
Map dataMap = new HashMap<>();
int index = 0;
// 列循环开始
for (int j = startCol; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String cellValue = null;
if (null != cell) {
cellValue = parseExcel(cell);
} else {
cellValue = "";
}
String key = "col" + j;
if (size > 0 && size > index) {
key = colKeys[index];
}
index++;
dataMap.put(key, cellValue);
}
if (!dataMap.isEmpty()) {
dataList.add(dataMap);
}
}
return dataList;
}
public static List> readExcel(MultipartFile target, int startrow, int startcol, int sheetnum) {
List> varList = new ArrayList>();
String name = target.getOriginalFilename();
if (isExcel2007(name)) {
varList = readExcel2007(target, startrow, startcol, sheetnum, null);
} else {
varList = readExcel2003(target, startrow, startcol, sheetnum, null);
}
return varList;
}
public static List> readExcel(MultipartFile target, int startrow, int startcol, int sheetnum, List cloumNameList) {
List> varList = new ArrayList>();
String name = target.getOriginalFilename();
if (isExcel2007(name)) {
varList = readExcel2007(target, startrow, startcol, sheetnum, cloumNameList);
} else {
varList = readExcel2003(target, startrow, startcol, sheetnum, cloumNameList);
}
return varList;
}
@SuppressWarnings("resource")
private static List> readExcel2003(MultipartFile target, int startrow, int startcol, int sheetnum, List cloumNameList) {
List> varList = new ArrayList>();
HSSFWorkbook wb = null;
try {
int size = 0;
if (cloumNameList != null && !cloumNameList.isEmpty()) {
size = cloumNameList.size();
}
wb = new HSSFWorkbook(target.getInputStream());
// sheet 从0开始
HSSFSheet sheet = wb.getSheetAt(sheetnum);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 行循环开始
for (int i = startrow; i < rowNum; i++) {
Map varpd = new HashMap(6);
// 行
HSSFRow row = sheet.getRow(i);
// 每行的最后一个单元格位置
int cellNum = row.getLastCellNum();
int index = 0;
// 列循环开始
for (int j = startcol; j < cellNum; j++) {
HSSFCell cell = row.getCell(j);
String cellValue = null;
if (null != cell) {
cellValue = parseExcel(cell).trim();
} else {
cellValue = "";
}
String var = "var" + j;
if (size > 0 && size > index) {
var = cloumNameList.get(index);
}
index++;
varpd.put(var, cellValue);
}
boolean isEmptyRow = true;
for (Map.Entry entry : varpd.entrySet()) {
if (!CommonUtil.isEmpty(entry.getValue().trim())) {
isEmptyRow = false;
break;
}
}
if (!isEmptyRow) {
varList.add(varpd);
}
}
} catch (Exception e) {
System.out.println(e);
} finally {
wb = null;
}
return varList;
}
@SuppressWarnings("resource")
private static List> readExcel2007(MultipartFile target, int startrow, int startcol, int sheetnum, List cloumNameList) {
List> varList = new ArrayList>();
XSSFWorkbook wb = null;
try {
int size = 0;
if (cloumNameList != null && !cloumNameList.isEmpty()) {
size = cloumNameList.size();
}
OPCPackage p = OPCPackage.open(target.getInputStream());
wb = new XSSFWorkbook(p);
// sheet 从0开始
XSSFSheet sheet = wb.getSheetAt(sheetnum);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 行循环开始
for (int i = startrow; i < rowNum; i++) {
// >100000条跳出，预防内存溢出
if (i > 100000) {
break;
}
Map varpd = new HashMap(6);
// 行
XSSFRow row = sheet.getRow(i);
// 每行的最后一个单元格位置
int cellNum = row.getLastCellNum();
int index = 0;
// 列循环开始
for (int j = startcol; j < cellNum; j++) {
XSSFCell cell = row.getCell(j);
String cellValue = null;
if (null != cell) {
cellValue = parseExcel(cell);
} else {
cellValue = "";
}
String var = "var" + j;
if (size > 0 && size > index) {
var = cloumNameList.get(index);
}
index++;
varpd.put(var, cellValue);
}
boolean isEmptyRow = true;
for (Map.Entry entry : varpd.entrySet()) {
if (!CommonUtil.isEmpty(entry.getValue().trim())) {
isEmptyRow = false;
break;
}
}
if (!isEmptyRow) {
varList.add(varpd);
}
}
} catch (Exception e) {
System.out.println(e);
} finally {
wb = null;
}
return varList;
}
@SuppressWarnings("resource")
public static List> readExcel2003(MultipartFile target, int startrow, int startcol, int sheetnum) {
List> varList = new ArrayList>();
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(target.getInputStream());
HSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { // 行循环开始
Map varpd = new HashMap();
HSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { // 列循环开始
HSSFCell cell = row.getCell(j);
String cellValue = null;
if (null != cell) {
cellValue = parseExcel(cell);
} else {
cellValue = "";
}
varpd.put("var" + j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
} finally {
wb = null;
}
return varList;
}
@SuppressWarnings("resource")
public static List> readExcel2007(MultipartFile target, int startrow, int startcol, int sheetnum) {
List> varList = new ArrayList>();
XSSFWorkbook wb = null;
try {
OPCPackage p = OPCPackage.open(target.getInputStream());
wb = new XSSFWorkbook(p);
XSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { // 行循环开始
if (i > 100000) {// >100000条跳出，预防内存溢出
break;
}
Map varpd = new HashMap();
XSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { // 列循环开始
XSSFCell cell = row.getCell(j);
String cellValue = null;
if (null != cell) {
cellValue = parseExcel(cell);
} else {
cellValue = "";
}
varpd.put("var" + j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
} finally {
wb = null;
}
return varList;
}
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\.(?i)(xlsx)$");
}
public static String parseExcel(Cell cell) {
String result = new String();
switch (cell.getCellType()) {
// 数字类型
case HSSFCell.CELL_TYPE_NUMERIC:
// 处理日期格式、时间格式
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {
// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58 || cell.getCellStyle().getDataFormat() == 181 || cell.getCellStyle().getDataFormat() == 183) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
// CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
// String temp = style.getDataFormatString();
// 单元格设置成常规
// if (temp.equals("General")) {
format.applyPattern("#");
// }
result = format.format(value);
}
break;
case HSSFCell.CELL_TYPE_STRING:
// String类型
result = cell.getRichStringCellValue().toString();
if ("NULL".equals(result)) {
result = null;
}
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式类型
// cell.getCellFormula();
try {
result = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
result = String.valueOf(cell.getRichStringCellValue());
}
// 如果是数字，小数点后保留一位，并且小数位是0
if (CommonUtil.isDouble(result) && result.indexOf(".") + 2 == result.length() && "0".equals(result.substring(result.length() - 1))) {
// 去掉小数部分
result = result.substring(0, result.indexOf("."));
}
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
return result.trim();
}
}
-------------------------以下是业务代码示例-------------------------
5.导出示例
@ApiOperation(value = "导出")
@GetMapping("/excel/download")
public void download(HttpServletResponse response) throws Exception {
// excel标题
List titleList = new ArrayList<>();
titleList.add("序号");
titleList.add("资产名称");
titleList.add("资产数量");
titleList.add("计量单位");
titleList.add("单价(元)");
titleList.add("总价(元)");
titleList.add("使用地点");
titleList.add("资产类别");
titleList.add("采购日期");
titleList.add("报废年限(年)");
titleList.add("使用情况");
// 创建模板 sheet名称
XSSFWorkbook workbook = ExcelWriteUtil.createTemplate2007("资产列表", 0, titleList);
//样式
CellStyle cellStyleCenter = ExcelWriteUtil.createCellStyle(workbook, "宋体", 14, IndexedColors.BLACK.getIndex(), false, false, false, true, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
//设置样式边框
cellStyleCenter = ExcelWriteUtil.setBorderStyle(cellStyleCenter, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN);
String name = "";
String state = "";
Integer typeId = null;
List list = assetDao.selectAssetList(name, state, typeId);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < list.size(); i++) {
//创建行
Row row = ExcelWriteUtil.createRow(workbook, 0, i + 1);
AssetsRtn data = list.get(i);
ExcelWriteUtil.createCell(row, 0, ExcelWriteUtil.objToString(i + 1), cellStyleCenter);
ExcelWriteUtil.createCell(row, 1, ExcelWriteUtil.objToString(data.getName()), cellStyleCenter);
ExcelWriteUtil.createCell(row, 2, ExcelWriteUtil.objToString(data.getAmount()), cellStyleCenter);
ExcelWriteUtil.createCell(row, 3, ExcelWriteUtil.objToString(data.getUnitText()), cellStyleCenter);
ExcelWriteUtil.createCell(row, 4, ExcelWriteUtil.objToString(data.getPrice()), cellStyleCenter);
double price = data.getPrice().doublevalue();
double totalPrice = price * data.getAmount();
DecimalFormat df = new DecimalFormat("#.00");
ExcelWriteUtil.createCell(row, 5, ExcelWriteUtil.objToString(df.format(totalPrice)), cellStyleCenter);
ExcelWriteUtil.createCell(row, 6, ExcelWriteUtil.objToString(data.getSite()), cellStyleCenter);
ExcelWriteUtil.createCell(row, 7, ExcelWriteUtil.objToString(data.getTypeText()), cellStyleCenter);
ExcelWriteUtil.createCell(row, 8, ExcelWriteUtil.objToString(sf.format(data.getCreateTime())), cellStyleCenter);
ExcelWriteUtil.createCell(row, 9, ExcelWriteUtil.objToString(data.getMaxUseYears()), cellStyleCenter);
String stateText = "";
if (data.getState().equals("1")) {
stateText = "使用中";
} else if (data.getState().equals("2")) {
stateText = "闲置";
} else {
stateText = "报废";
}
ExcelWriteUtil.createCell(row, 10, ExcelWriteUtil.objToString(stateText), cellStyleCenter);
}
// 设置列宽为自适应
XSSFSheet sheet = workbook.getSheetAt(0);
for (int i = 0; i < titleList.size(); i++) {
// 调整每一列宽度
sheet.autoSizeColumn(i);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
writeResponseExcel(response, workbook, "资产列表");
}
protected void writeResponseExcel(HttpServletResponse response, XSSFWorkbook workbook, String excelName) throws IOException {
// 1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("application/vnd.ms-excel");
// 2.设置文件头：最后一个参数是设置下载文件名(假如我们叫a.pdf)
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(excelName + ".xlsx", "utf-8");
response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
// 3.通过response获取ServletOutputStream对象(out)
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
workbook.close();
out.flush();
out.close();
}
6.模板下载示例
@ApiOperation(value = "模板下载")
@GetMapping("/excel/download/model")
public void downloadModel(HttpServletRequest request, HttpServletResponse response) throws Exception {
// excel标题
List titleList = new ArrayList<>();
titleList.add("资产名称");
titleList.add("资产数量");
titleList.add("计量单位");
titleList.add("单价(元)");
titleList.add("使用地点");
titleList.add("资产类别");
titleList.add("采购日期");
titleList.add("报废年限(年)");
titleList.add("使用情况");
//创建模板 sheet名称
XSSFWorkbook workbook = ExcelWriteUtil.createTemplate2007("资产列表模板", 0, titleList);
XSSFSheet sheet = workbook.getSheet("资产列表模板");
// 示例
List example = new ArrayList<>();
example.add("电脑");
example.add("5");
example.add("台");
example.add("888.00");
example.add("办公室");
example.add("办公用品");
example.add("2017-01-01 00:00:00");
example.add("10");
example.add("使用中");
ExcelWriteUtil.createExampleRow(workbook, "资产列表模板", example, 1);
CellStyle cellStyle = ExcelWriteUtil.createCellStyle(workbook, "宋体", 12, IndexedColors.BLACK.getIndex(), false, false, false, false, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
// 自动调整列宽
for (int i = 0; i < titleList.size(); i++) {
sheet.autoSizeColumn(i);
}
// 1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("application/vnd.ms-excel");
// 2.设置文件头：最后一个参数是设置下载文件名(假如我们叫a.pdf)
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("资产列表录入.xlsx", "utf-8");
response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
ServletOutputStream out;
try {
// 3.通过response获取ServletOutputStream对象(out)
out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
7.导入示例
@ApiOperation(value = "导入")
@PostMapping("/excel/upload")
public Object upload(MultipartFile file) {
//根据上传文件获取Workbook
Workbook workbook = ExcelReadUtil.getWorkbook(file);
if (workbook == null) {
return "请选择excel文件";
}
//检查模板
if (!ExcelReadUtil.valueTemplate(workbook, 0, 0, new String[]{"资产名称", "资产数量", "计量单位", "单价(元)", "使用地点", "资产类别", "采购日期", "报废年限(年)", "使用情况"})) {
return "请选择正确的excel模板";
}
//读数据
//读数据(从第二行开始读,第一行为示例,如果没有示例,则从第一行开始读)
List> list = ExcelReadUtil.readData(workbook, 0, 2, 0, new String[]{"name", "amount", "unitText", "price", "site", "typeText", "createTime", "maxUseYears", "state"});
//资产类别
List typeList = dictionaryService.queryCategoryOfAssetType(1);
//单位
List unitList = dictionaryService.queryCategoryOfAssetType(2);
List dataList = new ArrayList<>();
List errList = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
Assets data = new Assets();
Map row = list.get(i);
//名称
if (CommonUtil.isEmpty(row.get("name"))) {
errList.add(String.format("第%d行名称为空", i + 1));
} else {
data.setName(row.get("name"));
}
//数量
if (CommonUtil.isEmpty(row.get("amount"))) {
errList.add(String.format("第%d行数量为空", i + 1));
} else if (!CommonUtil.isInteger(row.get("amount"))) {
errList.add(String.format("第%d行数量不是整数", i + 1));
} else {
data.setAmount(Integer.valueOf(row.get("amount")));
}
//计量单位
Integer unitId = getDicId(unitList, row.get("unitText"));
if (CommonUtil.isEmpty(row.get("unitText"))) {
errList.add(String.format("第%d行计量单位为空", i + 1));
} else if (unitId == null) {
errList.add(String.format("第%d行计量单位未定义", i + 1));
} else {
data.setUnitId(unitId);
}
//单价
if (CommonUtil.notEmpty(row.get("price")) && !CommonUtil.isNumber(row.get("price"))) {
errList.add(String.format("第%d行采购单价不是数字", i + 1));
} else if (CommonUtil.notEmpty(row.get("price"))) {
data.setPrice(new BigDecimal(row.get("price")));
}
//使用地点
if (CommonUtil.isEmpty(row.get("site"))) {
errList.add(String.format("第%d行使用地点为空", i + 1));
} else {
data.setSite(row.get("site"));
}
//资产类别
Integer typeId = getDicId(typeList, row.get("typeText"));
if (CommonUtil.isEmpty(row.get("typeText"))) {
errList.add(String.format("第%d行资产类别为空", i + 1));
} else if (typeId == null) {
errList.add(String.format("第%d行资产类别未定义", i + 1));
} else {
data.setTypeId(typeId);
}
//采购日期
Date date = CommonUtil.parseDate(row.get("createTime"));
if (CommonUtil.notEmpty(row.get("createTime")) && date == null) {
errList.add(String.format("第%d行采购日期格式不正确", i + 1));
} else if (CommonUtil.notEmpty(row.get("createTime"))) {
data.setCreateTime(date);
}
//报废年限
if (CommonUtil.notEmpty(row.get("maxUseYears")) && !CommonUtil.isInteger(row.get("maxUseYears"))) {
errList.add(String.format("第%d行使用年限不是整数", i + 1));
} else if (CommonUtil.notEmpty(row.get("maxUseYears"))) {
data.setMaxUseYears(Integer.valueOf(row.get("maxUseYears")));
}
//使用情况
if (CommonUtil.isEmpty(row.get("state"))) {
errList.add(String.format("第%d行使用状态为空", i + 1));
} else if (row.get("state").indexOf("使用中")>0) {
data.setState("1");
} else if(row.get("state").indexOf("闲置")>0){
data.setState("2");
}else {
data.setState("3");
}
dataList.add(data);
}
if (errList.size() > 0) {
return errList;
}
int count = 0;
for (Assets assets : dataList) {
if (assetDao.saveAsset(assets)>0) {
count++;
}
}
//成功多少条
return count;
}
private Integer getDicId(List list, String name) {
for (baseDictionary dictionary : list) {
if (dictionary.getName().equals(name)) {
return dictionary.getId();
}
}
return null;
}
