读取excel内容，写入word表格

54 阅读 0 评论 36 点赞

我是靠谱客的博主欢呼鸡翅，最近开发中收集的这篇文章主要介绍读取excel内容，写入word表格，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

package springboot;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
import java.io.*;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class exportWord {
public static void main(String[] args) {
File file = new File("D:\chrom\1.xlsx");
List<List<String>> l = importExcel(file);
List<List<String>> rows = new ArrayList<>();
int total = 0;
for (List<String> list:l) {
total+=Integer.parseInt(list.get(4));
}
int count = 0;
for (List<String> list:l) {
count++;
List<String> row = new ArrayList<>();
row.add(list.get(8));
row.add(count+"."+list.get(3)+"（"+list.get(4)+"h，一般）");
row.add(total+"h");
rows.add(row);
}
exportWord(rows, "2018年10月工作总结");
}
//导出word
public static void exportWord(List<List<String>> list,String name) {
try {
XWPFDocument doc= new XWPFDocument();
//添加标题
XWPFParagraph titleParagraph = doc.createParagraph();
//设置段落居中
titleParagraph.setAlignment(ParagraphAlignment.CENTER);
XWPFRun titleParagraphRun = titleParagraph.createRun();
titleParagraphRun.setText(name);
titleParagraphRun.setColor("000000");
titleParagraphRun.setFontSize(20);
//表格
XWPFTable ComTable = doc.createTable();
//表格自适应宽度
CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();
comTableWidth.setType(STTblWidth.DXA);
comTableWidth.setW(BigInteger.valueOf(9072));
//表头
XWPFTableRow rowHead = ComTable.getRow(0);
XWPFParagraph cellParagraph = rowHead.getCell(0).getParagraphs().get(0);
XWPFRun cellParagraphRun
= cellParagraph.createRun();
String[] value_columns = {"工作", "具体内容", "总共耗时"};
for (int i = 0; i < value_columns.length; i++) {
if(i>0){
cellParagraph = rowHead.addNewTableCell().getParagraphs().get(0);
}
cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中
cellParagraphRun
= cellParagraph.createRun();
cellParagraphRun.setFontSize(14); //设置表头单元格居中
cellParagraphRun.setBold(true); //设置表头单元格加粗
cellParagraphRun.setText(value_columns[i]);
}
int rows = list.size();
//表格内容
for (int i = 0; i < rows; i++) {
XWPFTableRow rowsContent = ComTable.createRow();
for (int j = 0; j < value_columns.length; j++) {
XWPFParagraph cellParagraphC = rowsContent.getCell(j).getParagraphs().get(0);
if(j!=1){
cellParagraphC.setAlignment(ParagraphAlignment.CENTER); //设置表格内容居中
}
XWPFRun cellParagraphRunC
= cellParagraphC.createRun();
cellParagraphRunC.setFontSize(12); //设置表格内容字号
cellParagraphRunC.setText(list.get(i).get(j)+""); //单元格段落加载内容
}
}
if(rows==0) {
for (int i = 0; i < value_columns.length; i++) {
XWPFTableCell cell = ComTable.getRow(0).getCell(i);
cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
}
}else {
//设置居中
for (int i = 0; i <= rows; i++) {
for (int j = 0; j < value_columns.length; j++) {
XWPFTableCell cell = ComTable.getRow(i).getCell(j);
cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
}
}
}
mergeCellsVertically(ComTable,0,1,rows);
mergeCellsVertically(ComTable,2,1,rows);
FileOutputStream out = new FileOutputStream("C:\Users\dell\Desktop\"+name+".doc");
doc.write(out);
}catch (IOException e) {
e.printStackTrace();
}
}
// word跨列合并单元格
public static void mergeCellsHorizontal(XWPFTable table, int row, int fromCell, int toCell) {
for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) {
XWPFTableCell cell = table.getRow(row).getCell(cellIndex);
if ( cellIndex == fromCell ) {
// The first merged cell is set with RESTART merge value
cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
} else {
// Cells which join (merge) the first one, are set with CONTINUE
cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
}
}
}
// word跨行合并单元格
public static void mergeCellsVertically(XWPFTable table, int col, int fromRow, int toRow) {
for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++) {
XWPFTableCell cell = table.getRow(rowIndex).getCell(col);
if ( rowIndex == fromRow ) {
// The first merged cell is set with RESTART merge value
cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);
} else {
// Cells which join (merge) the first one, are set with CONTINUE
cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
}
}
}
//读取excel数据
public static List<List<String>> importExcel(File fl) {
List<List<String>> dataList = new ArrayList<List<String>>();
try {
Workbook workbook = null; // excel对象
String fileName = fl.getName().toLowerCase(); // 获取文件名
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(new FileInputStream(fl));
} else if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(new FileInputStream(fl));
} else {
throw new RuntimeException("您选择的文件不是一个Excel文件...");
}
Sheet sheet = workbook.getSheet("effort"); // 获取excel中的第一个表格
int rows = sheet.getLastRowNum();// 获取最后一行，即得到表格中的数据行数
if (rows == 0) {
throw new RuntimeException("表格中没有数据...");
}
Row row = null; // 行对象
Iterator<Cell> cols = null; // 列对象
List<String> list = null; // 用来存放一行数据
for (int i = 1; i <= rows; i++) { // 循环获取每一行的数据
row = sheet.getRow(i);
if (row != null) {
cols = row.cellIterator();
list = new ArrayList<String>();
while (cols.hasNext()) { // 循环获取每一列的数据存到list中
list.add(getCellValue(cols.next()));
}
dataList.add(list);// 将这一行数据存到dataList中
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return dataList;
}
//把读取的内容类型转成String
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
//把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}