废话没有,上Code
package com.org;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.XSSFWorkbook;
public class ReadAndWriteExcel {
public static void main(String[] args) {
try {
String excelUrl="D:\\Workspace-new-02\\ExcelDemo\\workbookNewCells.xlsx";
Workbook wb = WorkbookFactory.create(new File(excelUrl));
// 获取A1数据
String cellStr = wb.getSheetAt(0).getRow(0).getCell(0).toString();
// 去除{ }
cellStr=cellStr.replace("{", "").replace("}", "");
// 根据,拆成 String数组
String[] cellArry = cellStr.split(",");
// create a new workBook
Workbook wbNew = new XSSFWorkbook();
Sheet sheet1New = wbNew.createSheet("new sheet");
for(int i=0;i<cellArry.length;i++){
// 去除 \"
cellArry[i]=cellArry[i].replace("\\\"", "");
// 根据:拆成String数组
String[] strs=cellArry[i].split(":");
// Create a row and put some cells in it. Rows are 0 based. 新建第i行
Row rowNew = sheet1New.createRow((short)i);
// Create a cell and put a value in it.
rowNew.createCell(0).setCellValue(strs[0]);
// 判断是否可以转换成数字并插入cell
strs[1]=strs[1].trim();
if(canParseInt(strs[1])){
rowNew.createCell(1).setCellValue(Long.parseLong(strs[1]));
}else{
rowNew.createCell(1).setCellValue(strs[1]);
}
}
// 自适应列宽
sheet1New.autoSizeColumn(0);
// sheet1New.autoSizeColumn(1);
// HH表示24小时制 如果换成hh表示12小时制
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook"+sdf.format(new Date())+".xlsx");
wbNew.write(fileOut);
fileOut.close();
} catch (EncryptedDocumentException
| InvalidFormatException
| IOException e) {
e.printStackTrace();
}
}
private static boolean canParseInt(String str){
if(str == null){ //验证是否为空
return false;
}
// 使用正则表达式判断该字符串是否为数字,第一个\是转义符,\d+表示匹配1个或 //多个连续数字,"+"和"*"类似,"*"表示0个或多个
// return str.matches("\\d+");
return str.matches("\\d{0,4}");
}
}
备注:解析的是xlsx格式;
顺便对是否可以将String转换为Int用正则校验了下;
用到的包有:
(都是POI官方下载包一揽子的) 。