读取excel内容，写入word表格

朱雀 2020-10-01 02:23 1095阅读 0赞

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