读取excel内容，写入word表格

最新推荐文章于 2023-07-13 16:32:01 发布

Z丶royAl

最新推荐文章于 2023-07-13 16:32:01 发布

阅读量5.8w

点赞数 2

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_15260315/article/details/85287120

本文详细介绍了如何使用编程技术从Excel文件中读取数据，并将其准确地写入Word文档的表格中，实现数据的无缝迁移。无论你是进行报告自动化还是数据整合，这个过程都将帮助你提升效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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