maven依赖:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
工具类:
1.代码都是活的可以根据自己的业务修改,比如读取返还的数据格式不爽可以修改。
2.catch部分建议使用工程的日志系统 把异常记录。
package hoperun.pagoda.dxlAnalyse.utils;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.EncryptedDocumentException;
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.CellType;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Use poi Operate the Excel.
* @author zy
*/
public final class PoiUtils {
/**
* writeWorkbook.
*/
private static Workbook writeWorkbook;
/**
* readworkbook.
*/
private static Workbook readworkbook;
/**
* outFilepath.
*/
private static String writeFilePath;
/**
* readFilepath.
*/
private static String readFilePath;
/**
* excel xls.
*/
public static final String OFFICE_EXCEL_XLS = "xls";
/**
* excel xlsx.
*/
public static final String OFFICE_EXCEL_XLSX = "xlsx";
/**
* font size 12.
*/
public static final int FONT_SIZE_12 = 12;
/**
* cell width 15.
*/
public static final int CELL_DEFAULT_WIDTH = 20;
/**
* font size 16.
*/
public static final int FONT_SIZE_16 = 16;
/**
* construction.
*/
private PoiUtils() {
}
/**
* Gets the Workbook object based on the file path.
* @param filepath1 filepath
*/
public static void getWorkbook(final String filepath1) {
readFilePath = filepath1;
InputStream is = null;
if (readFilePath.isEmpty()) {
throw new IllegalArgumentException("The file path cannot be empty");
} else {
String suffiex = getSuffiex(readFilePath);
if (suffiex.isEmpty()) {
throw new IllegalArgumentException("The file suffix cannot be empty");
}
if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {
try {
is = new FileInputStream(readFilePath);
readworkbook = WorkbookFactory.create(is);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (EncryptedDocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if (is != null) {
is.close();
}
if (readworkbook != null) {
readworkbook.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} else {
throw new IllegalArgumentException("This file is not an Excel");
}
}
}
/**
* Also read the contents of the specified Sheet.
* @param sheetNo The sheet number, starting at 0, is set to null if sheetNo reads the full text.
* @return excel content.
*/
public static String readExcel(final Integer sheetNo) {
StringBuilder sb = new StringBuilder();
try {
getWorkbook(readFilePath);
if (readworkbook != null) {
if (sheetNo == null) {
int numberOfSheets = readworkbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = readworkbook.getSheetAt(i);
if (sheet == null) {
continue;
}
sb.append(readExcelSheet(sheet));
}
} else {
Sheet sheet = readworkbook.getSheetAt(sheetNo);
if (sheet != null) {
sb.append(readExcelSheet(sheet));
}
}
}
} catch (EncryptedDocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sb.toString();
}
/**
* read Excel Sheet.
* @param sheet sheet
* @return String
*/
private static String readExcelSheet(final Sheet sheet) {
StringBuilder sb = new StringBuilder();
if (sheet != null) {
int rowNos = sheet.getLastRowNum();// Get the total number of records in excel
for (int i = 0; i <= rowNos; i++) {
Row row = sheet.getRow(i);
if (row != null) {
int columNos = row.getLastCellNum();
for (int j = 0; j < columNos; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
cell.setCellType(CellType.STRING);
sb.append(cell.getStringCellValue() + " ");
}
}
}
}
}
return sb.toString();
}
/**
* Create Workbook.
* @param filepath1 filepath.
*/
public static void createWorkbook(final String filepath1) {
writeFilePath = filepath1;
if (writeFilePath.isEmpty()) {
throw new IllegalArgumentException("文件路径不能为空");
} else {
String suffiex = getSuffiex(writeFilePath);
if (suffiex.isEmpty()) {
throw new IllegalArgumentException("文件后缀不能为空");
}
if (OFFICE_EXCEL_XLS.equals(suffiex.toLowerCase())) {
writeWorkbook = new HSSFWorkbook();
} else {
writeWorkbook = new XSSFWorkbook();
}
}
}
/**
* creat Excel file.
* @param sheetName new sheet name.
* @param allData all data.
*/
public static void creatSheet(final String sheetName, final List<List<Object>> allData) {
//creat sheet.
Sheet sheet;
if (sheetName.isEmpty()) {
// use default sheet name.
sheet = writeWorkbook.createSheet();
} else {
sheet = writeWorkbook.createSheet(sheetName);
}
//set column width to default.
sheet.setDefaultColumnWidth((short) CELL_DEFAULT_WIDTH);
//creat style.
Map<String, CellStyle> styles = createStyles(writeWorkbook);
for(int rowIndex = 0; rowIndex<allData.size();rowIndex++) {
Row row = sheet.createRow(rowIndex);
List<Object> cellsData = allData.get(rowIndex);
for(int cellIndex = 0;cellIndex<cellsData.size();cellIndex++) {
Cell cell = row.createCell(cellIndex);
if(rowIndex == 0) {
cell.setCellStyle(styles.get("header"));
}else if ((rowIndex & 1)==1) {
//w 设置cell的样式
cell.setCellStyle(styles.get("cellA"));
} else {
cell.setCellStyle(styles.get("cellB"));
}
Object cellData = cellsData.get(cellIndex);
//Determine the type of object.
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (cellData instanceof Double) {
cell.setCellValue((Double) cellData);
} else if (cellData instanceof Date) {
String time = simpleDateFormat.format(cellData);
cell.setCellValue(time);
} else if (cellData instanceof Calendar) {
Calendar calendar = (Calendar) cellData;
String time = simpleDateFormat.format(calendar.getTime());
cell.setCellValue(time);
} else if (cellData instanceof Boolean) {
cell.setCellValue((Boolean) cellData);
} else {
if (cellData != null) {
cell.setCellValue(cellData.toString());
}
}
}
}
}
/**
* write Execute.
* @return result
*/
public static boolean writeExcel() {
boolean result = false;
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(writeFilePath);
writeWorkbook.write(outputStream);
result = true;
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
if (writeWorkbook != null) {
writeWorkbook.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
/**
* set style.
* @param wb Workbook.
* @return style Map.
*/
private static Map<String, CellStyle> createStyles(final Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
//title style.
CellStyle titleStyle = 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) FONT_SIZE_16);
titleFont.setBold(true);
titleFont.setFontName("微软雅黑");
titleStyle.setFont(titleFont);
styles.put("title", titleStyle);
//header style.
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setFillForegroundColor(IndexedColors.GREEN.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) FONT_SIZE_12);
headerFont.setColor(IndexedColors.WHITE.getIndex());
titleFont.setFontName("微软雅黑");
headerStyle.setFont(headerFont);
styles.put("header", headerStyle);
Font cellStyleFont = wb.createFont();
cellStyleFont.setFontHeightInPoints((short) FONT_SIZE_12);
cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
cellStyleFont.setFontName("微软雅黑");
//content style A.
CellStyle cellStyleA = 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);
//content style A:The foreground color is light yellow.
CellStyle cellStyleB = 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;
}
/**
* get suffix.
* @param path path.
* @return suffix
*/
private static String getSuffiex(final String path) {
if ( path.isEmpty()) {
return "";
}
int index = path.lastIndexOf(".");
if (index == -1) {
return "";
}
return path.substring(index + 1, path.length());
}
}
写入调用:
// 创建workbook
String outFilePath = "c://test.xls";
PoiUtils.createWorkbook(outFilePath);
//创建第一个sheet并写入数据
List<List<Object>> formDate = new ArrayList<List<Object>>();
List<Object> formTitle = Arrays.asList("Form一览","form名","Filed数","Button数","Code量");
formDate.add(....);//添加行的内容
disposalFormData(formCountMap, formDate);
String sheetName1 = "sheet名字";
PoiUtils.creatSheet(sheetName1, formDate);
//创建第二个sheet并写入数据
//......
//把workboot写入文件
PoiUtils.writeExcel()
读取调用:
String filePath = "c://test.xls";
PoiUtils.getWorkbook(filePath);
//读第一个sheet内容
String content = PoiUtils.readExcel(0);
//读所有sheet内容
String content1 = PoiUtils.readExcel(null);
poi 设置超链接
public void testSetHyperLink() {
XSSFWorkbook workbook = new XSSFWorkbook();
try {
FileOutputStream out = new FileOutputStream("d:\\testSetHyperLink.xlsx");
XSSFSheet sheet = workbook.createSheet("1");
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setUnderline(XSSFFont.U_DOUBLE);
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
CreationHelper createHelper = workbook.getCreationHelper();
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("test.xlsx");
XSSFFont font1 = workbook.createFont();
font1.setColor(HSSFColor.GREEN.index);
cellStyle.setFont(font1);
cell.setCellStyle(cellStyle);
Hyperlink hyperlink = createHelper.createHyperlink(Hyperlink.LINK_FILE);
hyperlink.setAddress("C://Users//Administrator//Desktop//test.xlsx");
cell.setHyperlink(hyperlink);
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}