java将数据存入excel中并保存本地

下面将由方法分开展示步骤，以及合并方法展示步骤两部分组成。
可直接观看第二部分合并后的方法即可，便于快速使用

1，分开方法展示步骤

分开方法展示java将数据存入excel中的步骤

//创建sheet页
public static void setSheet(String sheetName) {
workbook = new XSSFWorkbook();
sheet = workbook.createSheet(sheetName);
}

//创建表头
public static void createHead(List<String> headList) {
//创建表头，也就是第一行
row = sheet.createRow(0);
for (int i = 0; i < headList.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(headList.get(i));
}
}

//创建表内容
public static void createContent(List<List<String>> contentList) {
//创建表内容，从第二行开始
for (int i = 0; i < contentList.size(); i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < contentList.get(i).size(); j++) {
row.createCell(j).setCellValue(contentList.get(i).get(j));
}
}
}

//写入文件
public static void writeToFile(String filePath){
file = new File(filePath);
//将文件保存到指定的位置

try {
workbook.write(new FileOutputStream(file));
System.out.println("写入文件成功");
//workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}

// 内容测试数据
protected static List<List<String>> getContent() {
List<List<String>> contentList = new ArrayList<>();
List<String> content1 = new ArrayList<>();
content1.add("张三");
content1.add("18");
List<String> content2 = new ArrayList<>();
content2.add("李四");
content2.add("20");
contentList.add(content1);
contentList.add(content2);
return contentList;
}

public static void main(String[] args) {
//表头测试数据
List<String> headList = new ArrayList<>();
headList.add("昵称");
headList.add("年龄");
List<List<String>> contentList = getContent();//内容测试数据
setSheet("WorkSheet"); //创建sheet页
createHead(headList); //设置表头
createContent(contentList); //设置内容
writeToFile("D://work.xls"); //写入文件
}

2，进行方法的合并

2.1 合并方法，将java将数据存入excel中

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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class Export {

/*
* sheetName：表名
* heaaList:表头
* contentList:表内容
* filePath：写入文件地址
* */
public void exportToExcel(String sheetName, List<String> heaList, List<List<String>> contentList, String filePath){
//创建sheet页
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
//创建表头（第一行）
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < heaList.size() ; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(heaList.get(i));

}
//创建表内容
//List<List<String>>,一条内容存储多个列表对象
for (int i = 0; i <contentList.size() ; i++) {
row = sheet.createRow(i+1);
for (int j = 0; j < contentList.get(i).size(); j++) {
// row.createCell(j).setCellValue(contentList.get(i).get(j);
row.createCell(j).setCellValue(contentList.get(i).get(j));
}

}
// for (int i = 0; i < contentList.size(); i++) {
// row = sheet.createRow(i+1);
// row.createCell(i).setCellValue(String.valueOf(contentList.get(i)));
// }

//写入文件
File file = new File(filePath);
try {
//将文件保存到指定位置
workbook.write(new FileOutputStream(file));
System.out.println("写入文件成功");
} catch (IOException e) {
e.printStackTrace();
}

}

}

2.2 进行测试

package output.WriteToExcel;

import java.util.ArrayList;
import java.util.List;

public class test {
public static void main(String[] args) {
Export export = new Export();

/*
* sheetName：表名
* heaaList:表头
* contentList:表内容
* filePath：写入文件地址
* */

//表名：
String biaoming = "测试表";
//表头：
List<String> biaotou = new ArrayList<>();
biaotou.add("姓名");
biaotou.add("年龄");
biaotou.add("职业");
//表数据：
List<List<String>> contentList = new ArrayList<>();
List<String> content1 = new ArrayList<>();
content1.add("张三");
content1.add("20");
List<String> content2 = new ArrayList<>();
content2.add("李四");
content2.add("20");
contentList.add(content1);
contentList.add(content2);
//写入文件路径
String path = "D:\\work.xls";
export.exportToExcel(biaoming,biaotou,contentList,path);
}
}

3.改版代码

3.1 改版方法

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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class Export {

    /*
     * sheetName：表名
     * heaaList:表头
     * contentList:表内容
     * filePath：写入文件地址
     * */
    public void exportToExcel(String sheetName, List<String> heaList, List<String> contentList, String filePath){
        //创建sheet页
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //创建表头（第一行）
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < heaList.size() ; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(heaList.get(i));
        }
        //创建表内容
        //List<List<String>>,一条内容存储多个列表对象
//        for (int i = 0; i <contentList.size() ; i++) {
//            row = sheet.createRow(i+1);
//            for (int j = 0; j < contentList.get(i).size(); j++) {
                row.createCell(j).setCellValue(contentList.get(i).get(j);
//                row.createCell(j).setCellValue(contentList.get(i).get(j));
//            }
//        }
        XSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i < contentList.size(); i++) {
            XSSFCell cell = row1.createCell(i);
            cell.setCellValue(contentList.get(i));
        }
        //写入文件
        File file = new File(filePath);
        try {
            //将文件保存到指定位置
            workbook.write(new FileOutputStream(file));
            System.out.println("写入文件成功");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3.2 改版方法测试

public static void main(String[] args) {
    String uuid = UUIDUtil.uuid();
    //表数据:
    String[] arr01 = {"张三","18","120"};
    List<String> list = Arrays.asList(arr01);
    //表名:
    String biaoming = "数据元管理表"+uuid.substring(0,4);
    //表头:
    String[] arr02 = {"姓名","年龄","成绩"};
    List<String> biaotou = Arrays.asList(arr02);
    //写入文件路径:
    String filePath = "C:\\upload\\export\\"+uuid+".xlsx";
    export.exportToExcel(biaoming,biaotou,list,filePath);
}