import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelUtils {
public static ArrayList<Map<String, String>> readExcel(String path) throws Exception {
ArrayList<Map<String, String>> mapList = new ArrayList<>();
File file = new File(path);
//判断文件是否存在
if (file.isFile() && file.exists()) {
System.out.println(file.getPath());
//获取文件的后缀名 \\ .是特殊字符
String[] split = file.getName().split("\\.");
System.out.println(split[1]);
Workbook wb;
//根据文件后缀(xls/xlsx)进行判断
if ("xls".equals(split[1])) {
// //获取文件流对象
FileInputStream inputStream = new FileInputStream(file);
wb = new HSSFWorkbook(inputStream);
}else if (“xlsx”.equals(split[1])){
wb = new XSSFWorkbook(file);
}else {
System.out.println(“文件类型错误”);
return null;
}
//开始解析
Sheet sheet = wb.getSheetAt(0);
//第一行是列名,所以从第二行开始遍历
int firstRowNum = sheet.getFirstRowNum() + 1;
int lastRowNum = sheet.getLastRowNum();
//遍历行
for (int rIndex = firstRowNum; rIndex <= lastRowNum; rIndex++) {
Map map =new HashMap();
//获取当前行的内容
Row row = sheet.getRow(rIndex);
if (row != null) {
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
for (int cIndex = firstCellNum; cIndex < lastCellNum; cIndex++) {
String key = sheet.getRow(0).getCell(cIndex).getStringCellValue();
Cell cell=row.getCell(cIndex);
if (cell != null) {
cell.setCellType(CellType.STRING);
String value = row.getCell(cIndex).getStringCellValue();
//第一行中的作为键,第n行的作为值
map.put(key, value);
}else{
map.put(key, null);
}
}
}
mapList.add(map);
}
System.out.println("读取成功");
System.out.println(mapList);
}
return mapList;
}
public static void main(String[] args) throws Exception {
ArrayList<Map<String, String>> list= readExcel("D:/1.xlsx");
createExcel(list,2,new int[]{2,3});
}
private static void createExcel(ArrayList<Map<String, String>> list, int fileName,int[] arr) {
//存储路径--获取桌面位置
FileSystemView view = FileSystemView.getFileSystemView();
File directory = view.getHomeDirectory();
//存储Excel的路径
String path = directory+"\\"+fileName+".xlsx";
try {
//定义一个Excel表格
XSSFWorkbook wb = new XSSFWorkbook(); //创建工作薄
XSSFSheet sheet = wb.createSheet("sheet1"); //创建工作表
XSSFRow row = sheet.createRow(0); //行
XSSFCell cell; //单元格
int length=arr.length;
Map<String,String> map = list.get(0);
int i=0;
List<String> listArr=new ArrayList();
for(String key : map.keySet()){
for(int m=0;m<length;m++) {
if (i == arr[m]) {
listArr.add(key+"3DES");
}
}
row.createCell(i).setCellValue(key);
i++;
}
if(listArr!=null){
for(int m=0;m<listArr.size();m++) {
row.createCell(i).setCellValue(listArr.get(m));
i++;
}
}
//添加表头数据
for (int j = 0; j <list.size(); j++) {
//从前端接受到的参数封装成list集合,然后遍历下标从而取出对应的值
row = sheet.createRow(j+1); //行
int k=0;
Map<String,String> mapIt=list.get(j);
List<String> listData=new ArrayList();
for(String key : mapIt.keySet()){
for(int m=0;m<length;m++) {
if (k == arr[m]) {
listData.add(mapIt.get(key)+"3DES");
}
}
row.createCell(k).setCellValue(mapIt.get(key));
k++;
}
if(listData!=null){
for(int m=0;m<listData.size();m++) {
row.createCell(k).setCellValue(listData.get(m));
k++;
}
}
}
FileOutputStream outputStream = new FileOutputStream(path);
wb.write(outputStream);
outputStream.flush();
outputStream.close();
System.out.println("写入成功");
} catch (Exception e) {
System.out.println("写入失败");
e.printStackTrace();
}
}
}