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); }