excel读写

package com.test;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.google.common.collect.Maps;/** * Author: * Date: * Time: * Description:读取Excel数据 /public class ReadExcelTest { private XSSFSheet sheet; /* * 构造函数，初始化excel数据 * * @param filePath excel路径 * @param sheetName sheet表名 / ReadExcelTest(String filePath, String sheetName) { FileInputStream fileInputStream = null; try { fileInputStream = new FileInputStream(filePath); XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream); //获取sheet sheet = sheets.getSheet(sheetName); } catch (Exception e) { e.printStackTrace(); } } /* * 根据行和列的索引获取单元格的数据 * * @param row * @param column * @return / public String getExcelDateByIndex(int row, int column) { XSSFRow row1 = sheet.getRow(row); String cell = row1.getCell(column).toString(); return cell; } /* * 根据某一列值为“”的这一行，来获取该行第x列的值 * * @param caseName * @param currentColumn 当前单元格列的索引 * @param targetColumn 目标单元格列的索引 * @return */ public String getCellByCaseName(String caseName, int currentColumn, int targetColumn) { String operateSteps = “”; //获取行数 int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < rows; i++) { XSSFRow row = sheet.getRow(i); String cell = row.getCell(currentColumn).toString(); if (cell.equals(caseName)) { operateSteps = row.getCell(targetColumn).toString(); break; } } return operateSteps; } //打印excel数据 public List readExcelData(String key) { //获取行数 int rows = sheet.getPhysicalNumberOfRows(); HashMap<String, List> map = new HashMap<>(); for (int i = 0; i < rows; i++) { //获取列数 XSSFRow row = sheet.getRow(i); int columns = row.getPhysicalNumberOfCells(); if (i >= 1) { String left = row.getCell(0).toString(); String right = row.getCell(1).toString(); if (map.get(left) == null) { List list = new ArrayList<>(); list.add(right); map.put(left, list); } else { List list = map.get(left); list.add(right); map.put(left, list); } } } System.out.println("================="); map.keySet().forEach(v -> { System.out.println(v); System.out.println(map.get(v).size()); }); List list = map.get(key); return list; } public static void main(String[] args){ ReadExcelTest sheet1 = new ReadExcelTest(“D:\exceltest\baiqis_reject.xlsx”, “Sheet1”); List list= sheet1.readExcelData(“3”); Map<String, String> map = readfile(“D:\exceltest\data\3”); Map<String, Object> newData = new HashMap<>(); for (String index : list) { String value = map.get(index); newData.put(index, value); } List titles = new ArrayList(Arrays.asList(“userid”, “flow_no”)); try { writeExcel(“D:\exceltest\level-3.xlsx”, “sheel”,titles, newData); } catch (Exception e) { e.printStackTrace(); } } private static Map<String, String> readfile(String filepath) { Map<String, String> alldata = new HashMap<>(); try { File file = new File(filepath); if (!file.isDirectory()) { System.out.println(“文件”); System.out.println(“path=” + file.getPath()); System.out.println(“absolutepath=” + file.getAbsolutePath()); System.out.println(“name=” + file.getName()); } else if (file.isDirectory()) { String[] filelist = file.list(); for (int i = 0; i < filelist.length; i++) { File readfile = new File(filepath + “\” + filelist[i]); if (!readfile.isDirectory()) {// System.out.println(“path=” + readfile.getPath()); System.out.println(“absolutepath=” + readfile.getAbsolutePath()); ReadExcelTest sheet1 = new ReadExcelTest(readfile.getAbsolutePath(), “Sheet”); Map<String, String> map = sheet1.readExcelDataSub(); alldata.putAll(map); } else if (readfile.isDirectory()) { readfile(filepath + “\” + filelist[i]); } } } } catch (Exception e) { e.printStackTrace(); } return alldata; } public Map<String, String> readExcelDataSub() { //获取行数 int rows = sheet.getPhysicalNumberOfRows(); Map<String, String> userIdFlowNo = new HashMap<>(); for (int i = 0; i < rows; i++) { //获取列数 XSSFRow row = sheet.getRow(i); int columns = row.getPhysicalNumberOfCells(); if (i >= 1) { DecimalFormat df = new DecimalFormat(“0”); String cellValue1 = df.format(row.getCell(0).getNumericCellValue()); String left = row.getCell(0).toString(); String right = row.getCell(1).toString(); userIdFlowNo.put(cellValue1+"", right); } } return userIdFlowNo; } private static void setCell(Cell cell, Object object) { // 判断object的类型 SimpleDateFormat simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”); if (object instanceof Double) { cell.setCellValue((Double) object); } else if (object instanceof Date) { String time = simpleDateFormat.format((Date) object); cell.setCellValue(time); } else if (object instanceof Calendar) { Calendar calendar = (Calendar) object; String time = simpleDateFormat.format(calendar.getTime()); cell.setCellValue(time); } else if (object instanceof Boolean) { cell.setCellValue((Boolean) object); } else { if (object != null) { cell.setCellValue(object.toString()); } } } / * 创建Excel文件 * @param filepath filepath 文件全路径 * @param sheetName 新Sheet页的名字 * @param titles 表头 * @param values 每行的单元格 / public static void writeExcel(String filepath, String sheetName, List titles, Map<String, Object> values) throws IOException, FileNotFoundException { OutputStream outputStream = null; if (StringUtils.isBlank(filepath)) { throw new IllegalArgumentException(“文件路径不能为空”); } else { String suffiex = getSuffiex(filepath); if (StringUtils.isBlank(suffiex)) { throw new IllegalArgumentException(“文件后缀不能为空”); } Workbook workbook; if (“xls”.equals(suffiex.toLowerCase())) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } // 生成一个表格 Sheet sheet; if (StringUtils.isBlank(sheetName)) { // name 为空则使用默认值 sheet = workbook.createSheet(); } else { sheet = workbook.createSheet(sheetName); } // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成样式 Map<String, CellStyle> styles = createStyles(workbook); // 创建标题行 Row row = sheet.createRow(0); for (int i = 0; i < titles.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(styles.get(“header”)); String title = titles.get(i); cell.setCellValue(title); } // 写入正文 int index = 1; for (String key : values.keySet()) { row = sheet.createRow(index); Cell cell = row.createCell(0); setCell(cell, key); // 获取列的值 Object object = values.get(key); cell = row.createCell(1); setCell(cell, object); / 设置cell的样式 if (index % 2 == 1) { cell.setCellStyle(styles.get(“cellA”)); } else { cell.setCellStyle(styles.get(“cellB”)); }/ index++; } try { outputStream = new FileOutputStream(filepath); workbook.write(outputStream); } finally { if (outputStream != null) { outputStream.close(); } if (workbook != null) { workbook.close(); } } } } public static void writeExcelBak(String filepath, String sheetName, List titles, List<Map<String, Object>> values) throws IOException, FileNotFoundException { boolean success = false; OutputStream outputStream = null; if (StringUtils.isBlank(filepath)) { throw new IllegalArgumentException(“文件路径不能为空”); } else { String suffiex = getSuffiex(filepath); if (StringUtils.isBlank(suffiex)) { throw new IllegalArgumentException(“文件后缀不能为空”); } Workbook workbook; if (“xls”.equals(suffiex.toLowerCase())) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } // 生成一个表格 Sheet sheet; if (StringUtils.isBlank(sheetName)) { // name 为空则使用默认值 sheet = workbook.createSheet(); } else { sheet = workbook.createSheet(sheetName); } // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成样式 Map<String, CellStyle> styles = createStyles(workbook); // 创建标题行 Row row = sheet.createRow(0); // 存储标题在Excel文件中的序号 Map<String, Integer> titleOrder = Maps.newHashMap(); for (int i = 0; i < titles.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(styles.get(“header”)); String title = titles.get(i); cell.setCellValue(title); titleOrder.put(title, i); } // 写入正文 Iterator<Map<String, Object>> iterator = values.iterator(); // 行号 int index = 1; while (iterator.hasNext()) { row = sheet.createRow(index); Map<String, Object> value = iterator.next(); for (Map.Entry<String, Object> map : value.entrySet()) { // 获取列名 String title = map.getKey(); // 根据列名获取序号 int i = titleOrder.get(title); // 在指定序号处创建cell Cell cell = row.createCell(i); // 设置cell的样式 if (index % 2 == 1) { cell.setCellStyle(styles.get(“cellA”)); } else { cell.setCellStyle(styles.get(“cellB”)); } // 获取列的值 Object object = map.getValue(); // 判断object的类型 SimpleDateFormat simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”); if (object instanceof Double) { cell.setCellValue((Double) object); } else if (object instanceof Date) { String time = simpleDateFormat.format((Date) object); cell.setCellValue(time); } else if (object instanceof Calendar) { Calendar calendar = (Calendar) object; String time = simpleDateFormat.format(calendar.getTime()); cell.setCellValue(time); } else if (object instanceof Boolean) { cell.setCellValue((Boolean) object); } else { if (object != null) { cell.setCellValue(object.toString()); } } } index++; } try { outputStream = new FileOutputStream(filepath); workbook.write(outputStream); success = true; } finally { if (outputStream != null) { outputStream.close(); } if (workbook != null) { workbook.close(); } } } } / * 获取后缀 * @param filepath filepath 文件全路径 / private static String getSuffiex(String filepath) { if (StringUtils.isBlank(filepath)) { return “”; } int index = filepath.lastIndexOf("."); if (index == -1) { return “”; } return filepath.substring(index + 1, filepath.length()); } / * 设置格式 */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = Maps.newHashMap(); // 标题样式 XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐 titleStyle.setLocked(true); // 样式锁定 titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBold(true); titleFont.setFontName(“微软雅黑”); titleStyle.setFont(titleFont); styles.put(“title”, titleStyle); // 文件头样式 XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); // 前景色 headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 颜色填充方式 headerStyle.setWrapText(true); headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界 headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setColor(IndexedColors.WHITE.getIndex()); titleFont.setFontName(“微软雅黑”); headerStyle.setFont(headerFont); styles.put(“header”, headerStyle); Font cellStyleFont = wb.createFont(); cellStyleFont.setFontHeightInPoints((short) 12); cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex()); cellStyleFont.setFontName(“微软雅黑”); // 正文样式A XSSFCellStyle cellStyleA = (XSSFCellStyle) wb.createCellStyle(); cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置 cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleA.setWrapText(true); cellStyleA.setBorderRight(BorderStyle.THIN); cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderLeft(BorderStyle.THIN); cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderTop(BorderStyle.THIN); cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderBottom(BorderStyle.THIN); cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setFont(cellStyleFont); styles.put(“cellA”, cellStyleA); // 正文样式B:添加前景色为浅黄色 XSSFCellStyle cellStyleB = (XSSFCellStyle) wb.createCellStyle(); cellStyleB.setAlignment(HorizontalAlignment.CENTER); cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); cellStyleB.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyleB.setWrapText(true); cellStyleB.setBorderRight(BorderStyle.THIN); cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderLeft(BorderStyle.THIN); cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderTop(BorderStyle.THIN); cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderBottom(BorderStyle.THIN); cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setFont(cellStyleFont); styles.put(“cellB”, cellStyleB); return styles; }}