excel导入工具类
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
*@author xx
*@version Id:ImportExcelUtil.java, v 0.1 2017/1/11 9:34 wgy
*/
public class ImportExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
*
* @param in
* @param fileName
* @param columNum 自定义列数
* @return
* @throws Exception
*/
public List<List<String>> getBankListByExcel(InputStream in, String fileName,int columNum) throws Exception{
List<List<String>> list = null;
//创建Excel工作薄
Workbook work = this.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<String>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
//int totalRow = sheet.getLastRowNum();//如果excel有格式，这种方式取值不准确
int totalRow = sheet.getPhysicalNumberOfRows();
for (int j = sheet.getFirstRowNum(); j < totalRow; j++) {
row = sheet.getRow(j);
if(null!=row && !"".equals(row)){
//遍历所有的列
List<String> li = new ArrayList<String>();
int totalColum = row.getLastCellNum();
//int totalColum = row.getPhysicalNumberOfCells();
for (int y = row.getFirstCellNum(); y < columNum; y++) {
cell = row.getCell(y);
String cellVal = this.getCellValue(cell)+"";
li.add(cellVal);
}
list.add(li);
}
}
}
work.close();
return list;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
//2003-
wb = new HSSFWorkbook(inStr);
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误！");
}
return wb;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
if(null!=cell && !"".equals(cell)){
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}else{
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
}
return value;
}
}springmvc controller层调用方法
@ResponseBody
@RequestMapping(value = "/importExcel", method = RequestMethod.POST, produces = "application/json;charset=utf-8")
public BaseResponse upload(@RequestParam("excelFile") MultipartFile uploadFile, HttpServletRequest req) {
InputStream in =null;
BaseResponse b = new BaseResponse();
try{
List<List<String>> listObject = null;
in = uploadFile.getInputStream();
listObject = new ImportExcelUtil().getBankListByExcel(in,uploadFile.getOriginalFilename(),7);
b = appBasePathManager.saveExcelData(listObject);
int len = listObject.size();
}
catch (Exception ex){
ex.printStackTrace();
} finally {
try {
in.close();
}catch (Exception e){}
}
return b;
}//保存数据到数据库
public BaseResponse saveExcelData(List<List<String>> list){
PreAppBaseExcelImportRsp b = new PreAppBaseExcelImportRsp();
AppBasePathDO bo = new AppBasePathDO();
b.setSucc(true);
List<List<String>> notSaveData = new ArrayList<>();
if(null!=list && list.size()>1){
int len = list.size();
b.setTotalCount(len-1);
//遍历行，第一行是标题，跳过
for(int i=1;i<len;i++){
//遍历列
List<String> rows =list.get(i);
if(null!=rows){
int rowLen = rows.size();//
if(null!=rows.get(0) && !"".equals(rows.get(0)) &&
null!=rows.get(1) && !"".equals(rows.get(1)) &&
null!=rows.get(2) && !"".equals(rows.get(2)) &&
null!=rows.get(3) && !"".equals(rows.get(3))){
bo.setAppId(Integer.valueOf(rows.get(0)));
bo.setCreateTime(df.format(new Date()));
bo.setDataType("Excel");
//XX值重复性校验
Boolean isAppExist = checkXXExist(bo,"add");
if (!isAppExist) {
appBasePathMapper.create(bo);
} else {
rows.add("XXX");
notSaveData.add(rows);
}
}else{
rows.add("数据值不合法");
notSaveData.add(rows);
continue;
}
}
}
b.setNotSaveData(notSaveData);
if(null!=notSaveData){
b.setFailCount(notSaveData.size());
}
b.setSuccessCount(b.getTotalCount()-b.getFailCount());
}
return b;
}2.excel导出功能
springMVC controller层
@ResponseBody
@RequestMapping(value="/exportExcel",produces = {"application/vnd.ms-excel;charset=UTF-8"})
public String exportExcel(HttpServletResponse response) {
response.setContentType("application/x-msdownload;");
try {
String fileName = "xx数据_"+df.format(new Date());
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
ServletOutputStream outputStream = response.getOutputStream();
String[] titles = { "title1", "title2", "title3","title4","title5","title6","title7","title8" };
projectOsVersionManager.exportExcel(titles, outputStream);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}service层方法
public void exportExcel(String [] titles, OutputStream outputStream){
List<Object> list = xxxMapper.queryCommon(query);
// 创建一个workbook 对应一个excel应用文件
XSSFWorkbook workBook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = workBook.createSheet("认证版本");
sheet.setColumnWidth(0,5000*2); //调整第一列宽度
sheet.setColumnWidth(1,5000); //调整第二列宽度
sheet.autoSizeColumn((short)2); //调整第三列宽度
sheet.autoSizeColumn((short)3); //调整第四列宽度
sheet.setColumnWidth(4,5000); //调整第二列宽度
sheet.setColumnWidth(5,5000); //调整第二列宽度
sheet.setColumnWidth(6,5000); //调整第二列宽度
ExportExcelUtil exportUtil = new ExportExcelUtil(workBook, sheet);
XSSFCellStyle headStyle = exportUtil.getHeadStyle();
XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
// 构建表头
XSSFRow headRow = sheet.createRow(0);
XSSFCell cell = null;
for (int i = 0; i < titles.length; i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles[i]);
}
// 构建表体数据
if (list != null && list.size() > 0) {
for (int j = 0; j < list.size(); j++) {
XSSFRow bodyRow = sheet.createRow(j + 1);
Object items = list.get(j);
cell = bodyRow.createCell(0);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(1);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(2);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(3);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(4);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(5);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(6);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
cell = bodyRow.createCell(7);
cell.setCellStyle(bodyStyle);
cell.setCellValue(items.xx());
}
}
try{
workBook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}excel导出工具类
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
/**
*@author zz
*@version Id:ExportExcelUtil.java, v 0.1 2017/1/23 17:20 wgy
*/
public class ExportExcelUtil {
private XSSFWorkbook wb = null;
private XSSFSheet sheet = null;
/**
* @param wb
* @param sheet
*/
public ExportExcelUtil(XSSFWorkbook wb, XSSFSheet sheet)
{
this.wb = wb;
this.sheet = sheet;
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @param region
* @param cs
*/
public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)
{
int toprowNum = region.getFirstRow();
for (int i = toprowNum; i <= region.getLastRow(); i++)
{
XSSFRow row = sheet.getRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)
{
XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
// (short) j);
cell.setCellStyle(cs);
}
}
}
/**
* 设置表头的单元格样式
*
* @return
*/
public XSSFCellStyle getHeadStyle()
{
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
// 设置单元格字体样式
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 设置表体的单元格样式
*
* @return
*/
public XSSFCellStyle getBodyStyle()
{
// 创建单元格样式
XSSFCellStyle contentStyle = wb.createCellStyle();
contentStyle.setAlignment(CellStyle.ALIGN_LEFT);
contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
contentStyle.setBorderTop(CellStyle.BORDER_THIN);
contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
contentStyle.setBorderRight(CellStyle.BORDER_THIN);
contentStyle.setTopBorderColor(IndexedColors.BLACK.index);
contentStyle.setBottomBorderColor(IndexedColors.BLACK.index);
contentStyle.setLeftBorderColor(IndexedColors.BLACK.index);
contentStyle.setRightBorderColor(IndexedColors.BLACK.index);
return contentStyle;
}
}POI maven文件
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency>
