日常開發作業中對于檔案的相關操作大家多少都會涉及：上傳決議、資料匯出等，此篇內容主要分享一下作業中常用的Excel檔案的決議和匯出作業類實作，
實踐
1.maven依賴
首先引入POI包依賴
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2.API
大家需要了解一下相關的API類，便于后面理解代碼邏輯和排查問題，重寫相關邏輯實作自己的業務需求，
1.import org.apache.poi.ss.usermodel.Workbook,對應Excel檔案；
2.import org.apache.poi.hssf.usermodel.HSSFWorkbook，對應xls格式的Excel檔案；
3.import org.apache.poi.xssf.usermodel.XSSFWorkbook，對應xlsx格式的Excel檔案；
4.import org.apache.poi.ss.usermodel.Sheet，對應Excel檔案中的一個sheet；
5.import org.apache.poi.ss.usermodel.Row，對應一個sheet中的一行；
6.import org.apache.poi.ss.usermodel.Cell，對應一個單元格，
3.匯入
public class ImportExcelUtil {
private static final String EXCEL_XLS_SUFFIX = ".xls";
private static final String EXCEL_XLSX_SUFFIX = ".xlsx";
/**
* 讀取Excel資料內容
*
* @param rowIndex 指定行號
* @param columnIndex 指定列號
* @return Map 包含單元格資料內容的Map物件
*/
public static List<Map<Integer, Object>> readExcelContent(String filepath, Integer rowIndex, Integer columnIndex) throws Exception {
List<Map<Integer, Object>> returnList = new LinkedList<>();
Workbook wb = null;
Sheet sheet;
Row row;
try {
InputStream is = new FileInputStream(filepath);
if (filepath.endsWith(EXCEL_XLS_SUFFIX)) {
wb = new HSSFWorkbook(is);
} else if (filepath.endsWith(EXCEL_XLSX_SUFFIX)) {
wb = new XSSFWorkbook(is);
}
if (wb == null) {
throw new Exception("Workbook物件為空！");
}
sheet = wb.getSheetAt(0);
//決議檔案總行數、總列數
int rowNum = rowIndex != null ? rowIndex : sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = columnIndex != null ? columnIndex : row.getLastCellNum();
//回圈列
for (int colIndex = colNum; colIndex > 0; colIndex--) {
Cell cell = row.getCell(colIndex);
if (cell != null && !"".equals(cell.toString())) {
colNum = colIndex;
break;
}
}
logger.info("have data col:{}", colNum);
// 正文內容應該從第二行開始,第一行為表頭的標題
for (int i = 0; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
int size = (int) (colNum / .75f) + 1;
//存盤單元格資料
Map<Integer, Object> cellValue = new LinkedHashMap<>(size);
if (row == null) {
continue;
}
while (j <= colNum) {
Cell cell = row.getCell(j);
String value = "";
//日期單元格需格式化日期
if (cell != null) {
if (cell.getCellType() == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
value = formatter.format(d);
} else if (cell.toString().contains("E")) {
DecimalFormat nf = new DecimalFormat("0");
value = nf.format(cell.getNumericCellValue());
} else {
value = cell.toString().endsWith(".0") ? cell.toString().replace(".0", "") : cell.toString().trim();
}
} else if (cell.getCellType() == CellType.FORMULA) {
value = String.valueOf(cell.getNumericCellValue());
} else {
value = cell.toString().trim();
}
}
cellValue.put(j, value);
j++;
}
returnList.add(cellValueMap);
}
wb.close();
} catch (FileNotFoundException e) {
logger.error("FileNotFoundException", e);
} catch (IOException e) {
logger.error("IOException", e);
} finally {
if (wb != null) {
wb.close();
}
}
return returnList;
}
}
** 解釋： **
此處方法只是提供一個思路，后期大家可以根據自己的業務需求改寫，比如指定sheet，行號，列號等等，著重說明一下回傳值資料結構List，主要存盤結構為Map，key=列號，value=單元格內容；這種操作便于后期驗證必須列是否缺失、以及可以動態設計上傳檔案的列結構不必固定列位置等等，
4.匯出
public class ExportExcelUtil {
/**
* 匯出excel檔案，表頭為一維陣串列示不用合并單元格
* @param sheetName
* @param excelTitle
* @param dataCollection
* @param <T>
* @return
*/
public static<T> HSSFWorkbook exportExcel(String sheetName, String[] excelTitle, Collection<T> dataCollection) {
//創建一個Excel檔案
HSSFWorkbook workbook = new HSSFWorkbook();
//創建一個Sheet表格作業空間
HSSFSheet sheet = workbook.createSheet(sheetName);
HSSFCellStyle style = workbook.createCellStyle();
//設定表格默認寬度
sheet.setDefaultColumnWidth(20);
//設定表格的表頭
HSSFCell cellHeader;
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < excelTitle.length; i++) {
//創建單元格表頭
cellHeader = row.createCell(i);
cellHeader.setCellValue(new HSSFRichTextString(excelTitle[i]));
}
//匹配表頭設定單元格的值
setWorkBookValue(sheet, dataCollection,0, style);
return workbook;
}
/**
* （根據自定義）把具體資料寫入到excel中
* @param sheet
* @param dataCollection
* @param index
* @param style
* @param <T>
*/
@SuppressWarnings("unchecked")
private static<T> void setWorkBookValue(HSSFSheet sheet,Collection<T> dataCollection, int index,HSSFCellStyle style){
T t;
Object[] fields;
String fieldName;
String getMethodName;
HSSFCell cell;
HSSFRow row;
Class tClass;
Method getMethod;
Object value;
//遍歷集合設定單元格值
Iterator<T> it = dataCollection.iterator();
while(it.hasNext()){
//創建一行單元格
index ++;
row = sheet.createRow(index);
//獲取資料
t = it.next();
//利用反射，根據JavaBean屬性的先后順序，動態呼叫getXxx()方法得到屬性值
fields = t.getClass().getDeclaredFields();
for(int i = 0; i < fields.length; i++){
cell = row.createCell(i);
style.setAlignment(HorizontalAlignment.LEFT);
cell.setCellStyle(style);
//利用反射，根據JavaBean屬性的先后順序，動態呼叫getXxx()方法得到屬性值
Field[] newFields = t.getClass().getDeclaredFields();
fieldName = newFields[i].getName();
getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
tClass = t.getClass();
getMethod = tClass.getMethod(getMethodName, new Class[]{});
value = getMethod.invoke(t, new Object[]{});
setCellValue(value,cell);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* value格式校驗
*/
private static void setCellValue(Object value,HSSFCell cell){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String textValue = null;
Pattern pattern = Pattern.compile(RULE);
Matcher matcher;
HSSFRichTextString richTextString;
if (!StringUtils.isEmpty(value)){
//value進行型別轉換
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf(value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf(value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
} else if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它資料型別都當作字串簡單處理
textValue = value.toString();
}
if (textValue != null) {
matcher = pattern.matcher(textValue);
if (matcher.matches()) {
// 是數字當作double處理
cell.setCellValue(Double.parseDouble(textValue));
} else {
richTextString = new HSSFRichTextString(textValue);
cell.setCellValue(richTextString);
}
}
}
}
/**
* excel 匯出檔案
* @param response
* @param workbook
* @param fileName
* @throws IOException
*/
public static void exportExcelFile(HttpServletResponse response, HSSFWorkbook workbook, String fileName) throws IOException {
if (workbook != null) {
response.reset();
//指定下載的檔案名
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String filePrefix = sdf.format(new Date());
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(filePrefix + "_" + fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
BufferedOutputStream bufferedOutput = null;
try {
bufferedOutput = new BufferedOutputStream(response.getOutputStream());
workbook.write(bufferedOutput);
bufferedOutput.flush();
} catch (IOException e) {
e.printStackTrace();
throw e;
} finally {
if (bufferedOutput != null) {
try {
bufferedOutput.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
}
外部訪問匯出檔案示例：
public void exportFile(String objectJson, HttpServletResponse response) throws Exception {
....
//省略業務代碼
HSSFWorkbook workbook = ExportExcelUtil.exportExcel(SHEET_NAME, TITLE_LINE, xxxList);
ExportExcelUtil.exportExcelFile(response, workbook, FILE_NAME);
}
特殊說明：匯出操作實際上利用反射機制實作，則需保證Excel標題列順序需與匯出定義的物體物件屬性順序保持一致，否則會出現錯位現象，
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/170281.html
標籤：其他