导入pom.xml依赖 或者使用上传jar包
<!--读取excel文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
实现代码
public static void WriteExcelFile(Vector<String> files,String output) throws IOException {
/
HSSFWorkbook workbook = new HSSFWorkbook();
File exportFile = new File(output);
if (!exportFile.exists()) {
exportFile.createNewFile();
}
for(int i = 0 ;i < files.size() ;i++){
Vector<String> vetData = Myfile.ReadGetVetData(files.get(i));
String sheetName = GetSheetName(files.get(i));
Vector<Vector<String>> vetValue = GetVVTData(vetData);
WriterExcelSheet(workbook,vetValue,sheetName);
}
FileOutputStream fos = new FileOutputStream(output); // 创建输出流
workbook.write(fos); // 将数据写入输出流
fos.close(); // 关闭输出流
}
public static void WriteExcelFile(String file,String output) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
File exportFile = new File(output);
if (!exportFile.exists()) {
exportFile.createNewFile();
}
Vector<String> vetData = Myfile.ReadGetVetData(file);
String sheetName = GetSheetName(file);
Vector<Vector<String>> vetValue = GetVVTData(vetData);
WriterExcelSheet(workbook,vetValue,sheetName);
FileOutputStream fos = new FileOutputStream(output); // 创建输出流
workbook.write(fos); // 将数据写入输出流
fos.close(); // 关闭输出流
}
public static String StrHead="";
static Vector<Vector<String>> GetVVTData(Vector<String> vetData){
Vector<Vector<String>> result = new Vector<>();
String head ="NA\tNA";
if(StrHead!=""){
head=StrHead;
}
String[] strS = head.split("\t");
Vector<String> v = new Vector<>();
for(String s:strS){
v.add(s);
}
result.add(v);
for(int i = 0 ;i < vetData.size();i++){
strS = vetData.get(i).split("\t");
v = new Vector<>();
for(String s:strS){
if(s.contains(";")){
s = s.replace(";",";\r\n");
}
v.add(s);
}
result.add(v);
}
return result;
}
public static void WriterExcelSheet(HSSFWorkbook workbook, Vector<Vector<String>> vetValue, String SheetName) throws IOException {
if(SheetName==""){
SheetName="NA";
}
Sheet sheet = workbook.createSheet(SheetName); // 创建名为Sheet1的工作表
for(int i = 0 ;i < vetValue.size() ;i++){
Row row = sheet.createRow(i); // 创建第一行
for(int j = 0 ; j < vetValue.get(i).size() ;j++){
Cell cell = row.createCell(j); // 创建第一列
String svs = vetValue.get(i).get(j);
if(svs.contains("{") && svs.contains("}")){
svs = svs.replace("{","");
svs = svs.replace("}","");
svs = svs.replace(";",";\n");
}
cell.setCellValue(svs);
}
}
}
static String GetSheetName(String path){
String result = "NA";
return result;
}
public static void ReadExcelFile(String file) throws IOException {
FileInputStream inputStream = new FileInputStream(file);
//创建工作本,参数为工作流
Workbook workbook=null;
if(file.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
}else if(file.endsWith(".xlsx")){
workbook = new XSSFWorkbook(inputStream);
}
if(workbook!=null) {
// 获取第一个表单Sheet
Sheet sheetAt = workbook.getSheetAt(0);
for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
Row row = sheetAt.getRow(i);
// 读取每一格内容
for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
Cell cell = row.getCell(index);
if (cell.getStringCellValue().equals("")) {
continue;
}else{
System.out.println(cell.getStringCellValue());
}
}
}
}
}