poi工具类java,基于POI的Excel工具类

package com.kaishengit.excel;

import com.kaishengit.pojo.ShopDTO;

import org.apache.commons.lang.StringUtils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

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.usermodel.WorkbookFactory;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

/**

* Excel上传： 针对单元格数据类型转换数据格式

*

*

*/

public class ExcelUtils {

public static List readTickets(MultipartFile file, Integer matterId) throws Exception {

if(file == null || file.getSize() == 0)

return null;

if(matterId == null)

return null;

List tickets = new ArrayList();

// 文件名

String fileName = file.getOriginalFilename();

if (fileName.endsWith(".xlsx")||fileName.endsWith(".xls")) {

// 获取Excel文件对象

Workbook wb = WorkbookFactory.create(file.getInputStream());

// 获取文件指定工作表，默认第一个

Sheet sheet = wb.getSheetAt(0);

// 遍历行数(行记录，从0开始,首行记录数+1)

w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {

// 每一行对应一个list

List list = new ArrayList();

// 创建一个行对象

Row row = sheet.getRow(i);

if (row == null || row.getLastCellNum() == -1) {

break w;

}

// 遍历一行中的每个字段

for (int j = 0; j < row.getLastCellNum(); j++) {

// 为每一个字段创建一个单元格对象

Cell cell = row.getCell((short) (j));

if (cell == null) {

break w;

}

// 获取cell数据

String data = getCellType(cell);

list.add(data);

}

Ticket ticket = new Ticket();

ticket.setSncode(list.get(0));// 卡券券码

ticket.setMatterId(matterId);

ticket.setAllotFlag(Boolflag.FALSE);

ticket.setTakeFlag(Boolflag.FALSE);

tickets.add(ticket);

}

wb.close();

}

return tickets;

}

public static List readLotteriers(MultipartFile file) throws Exception {

if(file == null || file.getSize() == 0)

return null;

List lotteriers = new ArrayList();

// 文件名

String fileName = file.getOriginalFilename();

if (fileName.endsWith(".xlsx")||fileName.endsWith(".xls")) {

// 获取Excel文件对象

Workbook wb = WorkbookFactory.create(file.getInputStream());

// 获取文件指定工作表，默认第一个

Sheet sheet = wb.getSheetAt(0);

// 遍历行数(行记录，从0开始,首行记录数+1)

w: for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {

// 每一行对应一个list

List list = new ArrayList();

// 创建一个行对象

Row row = sheet.getRow(i);

if (row == null || row.getLastCellNum() == -1) {

break w;

}

// 遍历一行中的每个字段

for (int j = 0; j < row.getLastCellNum(); j++) {

// 为每一个字段创建一个单元格对象

Cell cell = row.getCell((short) (j));

if (cell == null) {

break w;

}

// 获取cell数据

String data = getCellType(cell);

list.add(data);

}

/*Lotterier lotterier = new Lotterier();

lotterier.setAccount(list.get(0));// 用户账号

lotterier.setAllotFlag(Boolflag.FALSE);

lotteriers.add(lotterier);*/

ShopDTO shopDTO = new ShopDTO();

shopDTO.setShopId(Integer.valueOf(list.get(0)));

shopDTO.setShopName(list.get(1));

shopDTO.setAddress(list.get(2));

lotteriers.add(shopDTO);

System.out.println("--------------------------");

System.out.println(list.get(0));

System.out.println(list.get(1));

System.out.println(list.get(2));

}

wb.close();

}

return lotteriers;

}

/**

* EXCEL中的CELL值转换

*

* @param cell

* @return

*/

public static String getCellType(Cell cell) {

String result = null;

switch (cell.getCellType()) {

case XSSFCell.CELL_TYPE_NUMERIC: // 数字类型，日期类型

result = dealNum(cell);

break;

case XSSFCell.CELL_TYPE_STRING: // 字符串类型

result = cell.getStringCellValue();

result = dealStr(result);

break;

case XSSFCell.CELL_TYPE_FORMULA: // 公式

result = cell.getCellFormula();

break;

default:

result = "";

break;

}

return result;

}

/**

* 处理数值类型

*

* @param cell

* @return

*/

public static String dealNum(Cell cell) {

String str = "";

if (HSSFDateUtil.isCellDateFormatted(cell)) {

Date cellDate = cell.getDateCellValue();

//str = TimeUtils.formatAll(cellDate);

} else {

str = new DecimalFormat("0").format(cell.getNumericCellValue());

}

return str;

}

/**

* 处理字符串

*

* @param str

* @return

*/

public static String dealStr(String str) {

if (str.trim().equals("") || str.trim().length() < 0) {

str = "";

}

return str;

}

public static byte[] createExcel(List coupons, String sheetName){

byte[] byteArray = null;

// 1.创建一个workbook，对应一个Excel文件

Workbook wb = new XSSFWorkbook();

// 2.在workbook中添加一个sheet，对应Excel中的一个sheet

Sheet sheet = null;

if (StringUtils.isBlank(sheetName)) {

sheet = wb.createSheet("XXX表");

} else {

sheet = wb.createSheet(sheetName);

}

// 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short

Row row = sheet.createRow((int) 0);

// 4.创建单元格，设置值表头，设置表头居中

CellStyle style = wb.createCellStyle();

// 居中格式

style.setAlignment(CellStyle.ALIGN_CENTER);

// 设置表头

Cell cell = row.createCell(0);

cell.setCellValue("卡券号码");

cell.setCellStyle(style);

cell = row.createCell(1);

cell.setCellValue("卡券密码");

cell.setCellStyle(style);

cell = row.createCell(2);

cell.setCellValue("主题码");

cell.setCellStyle(style);

// 循环将数据写入Excel

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

Coupon coupon = coupons.get(i);

row = sheet.createRow((int) i + 1);

// 创建单元格，设置值

row.createCell(0).setCellValue(coupon.getCouponCode());

row.createCell(1).setCellValue(coupon.getCouponPwd());

row.createCell(2).setCellValue(coupon.getThemeCode());

}

ByteArrayOutputStream os = new ByteArrayOutputStream();

try {

wb.write(os);

byteArray = os.toByteArray();

} catch (IOException e1) {

e1.printStackTrace();

}finally {

try {

wb.close();

os.close();

} catch (IOException e) {

e.printStackTrace();

}

}

return byteArray;

}

}