123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384 |
- package com.mtgg.utils;
- import com.mtgg.bean.PayLogDTO;
- import com.mtgg.bean.PoiExcelBean;
- import org.apache.poi.hssf.usermodel.*;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.apache.poi.xssf.usermodel.*;
- import java.beans.IntrospectionException;
- import java.beans.PropertyDescriptor;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.lang.reflect.InvocationTargetException;
- import java.lang.reflect.Method;
- import java.math.BigDecimal;
- import java.text.DecimalFormat;
- import java.text.NumberFormat;
- import java.text.ParseException;
- import java.text.SimpleDateFormat;
- import java.util.*;
- /**
- * Created by liuhaixiang on 2017/excels_station/5.
- */
- public class ExcelUtil {
- private final static String excel2003L =".xls"; //2003- 版本的excel
- private final static String excel2007U =".xlsx"; //2007+ 版本的excel
- /**
- * Excel导入
- */
- public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception{
- List<List<Object>> list = null;
- //1 根据流,获取workbook
- Workbook work = getWorkbook(in,fileName);
- if(null == work){ throw new Exception("创建Excel工作薄为空!"); }
- //2 三要素,sheet,row,cell
- Sheet sheet = null;
- Row row = null;
- Cell cell = null;
- list = new ArrayList<List<Object>>();
- //3 遍历sheet
- for (int i = 0; i < work.getNumberOfSheets(); i++) {
- sheet = work.getSheetAt(i);
- if(sheet==null){continue;}
- //遍历sheet中所有行,包含头部行
- for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
- //读取一行
- row = sheet.getRow(j);
- //去掉空行和表头
- if(row==null||row.getFirstCellNum()==j){continue;}
- //遍历所有的列
- List<Object> li = new ArrayList<Object>();
- for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
- cell = row.getCell(y);
- li.add(getCellValue(cell));
- }
- list.add(li);
- }
- }
- return list;
- }
- /**
- * 描述:根据文件后缀,自适应上传文件的版本
- */
- public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
- Workbook wb = null;
- String fileType = fileName.substring(fileName.lastIndexOf("."));
- if(excel2003L.equals(fileType)){
- wb = new HSSFWorkbook(inStr); //2003-
- }else if(excel2007U.equals(fileType)){
- wb = new XSSFWorkbook(inStr); //2007+
- }else{
- throw new Exception("解析的文件格式有误!");
- }
- return wb;
- }
- /**
- * 描述:对表格中数值进行格式化
- */
- public static Object getCellValue(Cell cell){
- Object value = null;
- DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字
- SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
- DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
- 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;
- }
- /**
- * 导入Excel表结束
- * 导出Excel表开始
- * @param sheetName 工作簿名称
- * @param clazz 数据源model类型
- * @param objs excel标题列以及对应model字段名
- * @param map 标题列行数以及cell字体样式
- */
- public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map<Integer, List<PoiExcelBean>> map, String sheetName) throws
- IllegalArgumentException,IllegalAccessException,InvocationTargetException,
- ClassNotFoundException, IntrospectionException, ParseException {
- // 创建新的Excel工作簿
- XSSFWorkbook workbook = new XSSFWorkbook();
- // 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称
- XSSFSheet sheet = workbook.createSheet(sheetName);
- // 以下为excel的字体样式以及excel的标题与内容的创建,下面会具体分析;
- createFont(workbook); //字体样式
- createTableHeader(sheet, map); //创建标题(头)
- createTableRows(sheet, map, objs, clazz); //创建内容
- return workbook;
- }
- private static XSSFCellStyle fontStyle;
- private static XSSFCellStyle fontStyle2;
- public static void createFont(XSSFWorkbook workbook) {
- // 表头
- fontStyle = workbook.createCellStyle();
- XSSFFont font1 = workbook.createFont();
- font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
- font1.setFontName("黑体");
- font1.setFontHeightInPoints((short) 14);// 设置字体大小
- fontStyle.setFont(font1);
- fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
- fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
- fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
- fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
- fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
- // 内容
- fontStyle2=workbook.createCellStyle();
- XSSFFont font2 = workbook.createFont();
- font2.setFontName("宋体");
- font2.setFontHeightInPoints((short) 10);// 设置字体大小
- fontStyle2.setFont(font2);
- fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
- fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
- fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
- fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
- fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
- }
- /**
- * 根据ExcelMapping 生成列头(多行列头)
- *
- * @param sheet 工作簿
- * @param map 每行每个单元格对应的列头信息
- */
- public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<PoiExcelBean>> map) {
- int startIndex=0;//cell起始位置
- int endIndex=0;//cell终止位置
- for (Map.Entry<Integer, List<PoiExcelBean>> entry : map.entrySet()) {
- XSSFRow row = sheet.createRow(entry.getKey());
- List<PoiExcelBean> excels = entry.getValue();
- for (int x = 0; x < excels.size(); x++) {
- //合并单元格
- if(excels.get(x).getCols()>1){
- if(x==0){
- endIndex+=excels.get(x).getCols()-1;
- CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
- sheet.addMergedRegion(range);
- startIndex+=excels.get(x).getCols();
- }else{
- endIndex+=excels.get(x).getCols();
- CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
- sheet.addMergedRegion(range);
- startIndex+=excels.get(x).getCols();
- }
- XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
- cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
- if (excels.get(x).getCellStyle() != null) {
- cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
- }
- cell.setCellStyle(fontStyle);
- }else{
- XSSFCell cell = row.createCell(x);
- cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
- if (excels.get(x).getCellStyle() != null) {
- cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
- }
- cell.setCellStyle(fontStyle);
- }
- }
- }
- }
- public static void createTableRows(XSSFSheet sheet, Map<Integer, List<PoiExcelBean>> map, List objs, Class clazz)
- throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
- ClassNotFoundException, ParseException {
- int rowindex = map.size();
- int maxKey = 0;
- List<PoiExcelBean> ems = new ArrayList<PoiExcelBean>();
- for (Map.Entry<Integer, List<PoiExcelBean>> entry : map.entrySet()) {
- if (entry.getKey() > maxKey) {
- maxKey = entry.getKey();
- }
- }
- ems = map.get(maxKey);
- List<Integer> widths = new ArrayList<Integer>(ems.size());
- for (Object obj : objs) {
- XSSFRow row = sheet.createRow(rowindex);
- for (int i = 0; i < ems.size(); i++) {
- PoiExcelBean em = (PoiExcelBean) ems.get(i);
- // 获得get方法
- PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
- Method getMethod = pd.getReadMethod();
- Object rtn = getMethod.invoke(obj);
- String value = "";
- // 如果是日期类型进行转换
- if (rtn != null) {
- if (rtn instanceof Date) {
- value = Double.toString(DateUtil.getExcelDate((Date)rtn));
- } else if(rtn instanceof BigDecimal){
- NumberFormat nf = new DecimalFormat("#,##0.00");
- value=nf.format((BigDecimal)rtn).toString();
- } else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){
- value="--";
- }else {
- value = rtn.toString();
- }
- }
- XSSFCell cell = row.createCell(i);
- cell.setCellValue(value);
- cell.setCellType(XSSFCell.CELL_TYPE_STRING);
- cell.setCellStyle(fontStyle2);
- // 获得最大列宽
- int width = value.getBytes().length * 300;
- // 还未设置,设置当前
- if (widths.size() <= i) {
- widths.add(width);
- continue;
- }
- // 比原来大,更新数据
- if (width > widths.get(i)) {
- widths.set(i, width);
- }
- }
- rowindex++;
- }
- // 设置列宽
- for (int index = 0; index < widths.size(); index++) {
- Integer width = widths.get(index);
- width = width < 2500 ? 2500 : width + 300;
- width = width > 10000 ? 10000 + 300 : width + 300;
- sheet.setColumnWidth(index, width);
- }
- }
- /**
- * @Author: MTGG
- * @Date: 15:22 2018/5/9
- * @Describe: 读取excel
- */
- public static List<List<String>> getExcel(String file_path) {
- List<List<String>> list = new ArrayList<List<String>>();
- try {
- InputStream is = new FileInputStream(file_path);
- Workbook wb = null;
- if (file_path.endsWith("xls")) {
- wb = new HSSFWorkbook(is);
- } else if (file_path.endsWith("xlsx")) {
- wb = new XSSFWorkbook(is);
- }
- int i = 0;
- Sheet sheet = wb.getSheetAt(0);
- Iterator rows = sheet.rowIterator();
- String str = null;
- while (rows.hasNext()) {
- List<String> dataList = new ArrayList<String>();
- PayLogDTO dataDoDTO = new PayLogDTO();
- Row row = (Row) rows.next();
- Iterator cells = row.cellIterator();
- while (cells.hasNext()) {
- Cell cell = (Cell) cells.next();
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_NUMERIC://数字
- if (DateUtil.isCellDateFormatted(cell)) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
- str = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
- break;
- }
- DecimalFormat format = new DecimalFormat("#");
- Number value = cell.getNumericCellValue();
- str = format.format(value);
- dataList.add(str);
- break;
- case Cell.CELL_TYPE_STRING://字符串
- if (i == 0){
- continue;
- }
- str = cell.getStringCellValue();
- dataList.add(str);
- break;
- case Cell.CELL_TYPE_BOOLEAN://Boolean
- System.out.print(cell.getBooleanCellValue() + "--boolean\t");
- break;
- case Cell.CELL_TYPE_FORMULA://公式
- System.out.print(cell.getCellFormula() + "\tb");
- break;
- default:
- System.out.print("unsuported sell type" + "\t");
- break;
- }
- }
- list.add(dataList);
- i++;
- }
- is.close();
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- return list;
- }
- /**
- * @Author: MTGG
- * @Date: 15:25 2018/5/9
- * @Describe: 写入excel 追加数据
- */
- public static boolean writeToExcel(PayLogDTO dataDoDTO,String filePath) {
- try {
- FileInputStream fs=new FileInputStream(filePath); //获取d://test.xls
- POIFSFileSystem ps=new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息
- HSSFWorkbook wb=new HSSFWorkbook(ps);
- HSSFSheet sheet=wb.getSheetAt(0); //获取到工作表,因为一个excel可能有多个工作表
- sheet.setDefaultColumnWidth((short) 40); //宽度制定
- HSSFCellStyle style = wb.createCellStyle(); //制作样式
- //设置 颜色
- style.setFillForegroundColor(HSSFColor.WHITE.index);
- //制作字体字号
- HSSFFont font = wb.createFont();
- font.setColor(HSSFColor.VIOLET.index);
- font.setFontHeightInPoints((short) 15);
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- style.setFont(font);
- HSSFRow row=sheet.getRow(0); //获取第一行(excel中的行默认从0开始,所以这就是为什么,一个excel必须有字段列头),即,字段列头,便于赋值
- System.out.println(sheet.getLastRowNum()+" "+row.getLastCellNum()); //分别得到最后一行的行号,和一条记录的最后一个单元格
- FileOutputStream out=new FileOutputStream(filePath); //向d://test.xls中写数据
- row=sheet.createRow((short)(sheet.getLastRowNum()+1)); //在现有行号后追加数据
- row.setRowStyle(style);
- //追加数据
- row.createCell(0).setCellValue(dataDoDTO.getAccount()); //设置第一个(从0开始)单元格的数据
- row.createCell(1).setCellValue(dataDoDTO.getTotalFee()); //设置第二个(从0开始)单元格的数据
- row.createCell(2).setCellValue(dataDoDTO.getDate());
- out.flush();
- wb.write(out);
- out.close();
- System.out.println(row.getPhysicalNumberOfCells()+" "+row.getLastCellNum());
- }catch (Exception e){
- System.out.println("写excel出错");
- }
- return true;
- }
- }
|