poi 操作excel和word(修改样式和内容)
package com.zhibei.utils;
import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import com.zhibei.otldb.api.Api;
import com.zhibei.otldb.mapper.OtlColumn;
import com.zhibei.otldb.mapper.OtlMapperManager;
import com.zhibei.otldb.mapper.OtlNColumn;
import com.zhibei.pojo.Record;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.POIXMLTextExtractor;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
public class PoiUtil {
private static OtlColumn otlUnit;
private static OtlNColumn otlnColumn ;
public static ListimportXLS(){
ArrayListlist = new ArrayList<>();
try {
//1、获取文件输入流
InputStream inputStream = new FileInputStream("E:\\文档\\poi\\级软件工程1班.xls");
//2、获取Excel工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//3、得到Excel工作表对象
HSSFSheet sheetAt = workbook.getSheetAt(0);
//4、循环读取表格数据
for (Row row : sheetAt) {
//首行(即表头)不读取
if (row.getRowNum() == 0) {
continue;
}
//读取数据前设置单元格类型
row.getCell(0).setCellType(CellType.STRING);
//读取当前行中单元格数据，索引从0开始
String id = row.getCell(0).getStringCellValue();
String groupName = row.getCell(1).getStringCellValue();
String adminiStrator = row.getCell(2).getStringCellValue();
String userName = row.getCell(3).getStringCellValue();
String politicalStatus = row.getCell(4).getStringCellValue();
row.getCell(5).setCellType(CellType.STRING);
String Contactinformation = row.getCell(5).getStringCellValue();
String fullName = row.getCell(6).getStringCellValue();
String reason = row.getCell(7).getStringCellValue();
Record record = new Record();
record.setAdminiStrator(adminiStrator);
record.setContactinformation(Contactinformation);
record.setFullName(fullName);
record.setGroupName(groupName);
record.setId(id);
record.setPoliticalStatus(politicalStatus);
record.setReason(reason);
record.setUserName(userName);
list.add(record);
}
//5、关闭流
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
public static void exportExcel(Listlist ) throws IOException {
//1.在内存中创建一个excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//2.创建工作簿
HSSFSheet sheet = hssfWorkbook.createSheet("软件团员关系转出");
Font font = hssfWorkbook.createFont();
font.setBold(true);
font.setColor((short) 13);
font.setFontHeightInPoints((short) 24);
font.setFontName("Arial");
CellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setFont(font);
//3.创建标题行
HSSFRow titlerRow = sheet.createRow(0);
titlerRow.createCell(0).setCellValue("团员id");
titlerRow.createCell(1).setCellValue("目前所在团支部");
titlerRow.createCell(2).setCellValue("团支书");
titlerRow.createCell(3).setCellValue("团员姓名");
titlerRow.createCell(4).setCellValue("政治面貌");
titlerRow.createCell(5).setCellValue("手机");
titlerRow.createCell(6).setCellValue("转出团支部");
titlerRow.createCell(7).setCellValue("原因");
//4.遍历数据,创建数据行
for (Record record : list) {
//获取最后一行的行号
int lastRowNum = sheet.getLastRowNum();
HSSFRow dataRow = sheet.createRow(lastRowNum + 1);
dataRow.createCell(0).setCellValue(record.getId());
Cell cell =dataRow.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue(otlUnit.encrypt(record.getGroupName()));
dataRow.createCell(2).setCellValue(record.getAdminiStrator());
dataRow.createCell(3).setCellValue(record.getUserName());
dataRow.createCell(4).setCellValue(record.getPoliticalStatus());
dataRow.createCell(5).setCellValue(record.getContactinformation());
dataRow.createCell(6).setCellValue(record.getFullName());
dataRow.createCell(7).setCellValue(record.getReason());
}
// 输出Excel文件
try {
FileOutputStream fos = new FileOutputStream(new File("/软件团员关系转出.xls"));
hssfWorkbook.write(fos);
hssfWorkbook.close();
fos.close();
System.out.println("生成excel文档成功");
} catch (Exception e) {
e.printStackTrace();
System.out.println("生成excel文档失败");
}
}
public static String readWord(String path) {
String buffer = "";
try {
if (path.endsWith(".doc")) {
InputStream is = new FileInputStream(new File(path));
WordExtractor ex = new WordExtractor(is);
buffer = ex.getText();
} else if (path.endsWith("doc")) {
OPCPackage opcPackage = POIXMLDocument.openPackage(path);
POIXMLTextExtractor extractor = new XWPFWordExtractor(opcPackage);
//extractor.hashCode("我");
buffer = extractor.getText();
}else if (path.endsWith("docx")) {
OPCPackage opcPackage = POIXMLDocument.openPackage(path);
POIXMLTextExtractor extractor = new XWPFWordExtractor(opcPackage);
buffer = extractor.getText();
extractor.close();
} else {
System.out.println("此文件不是word文件！");
}
} catch (Exception e) {
e.printStackTrace();
}
return buffer;
}
/**
*
* @param inputUrl 模板路径
* @param outputUrl 模板保存路径
*/
public static void changeWord(String inputUrl, String outputUrl,int status){
try {
//获取word文档解析对象
XWPFDocument doucument = new XWPFDocument(POIXMLDocument.openPackage(inputUrl));
//获取段落文本对象
Listparagraphs = doucument.getParagraphs();
for (int i=0;iruns = xwpfParagraph.getRuns();
for (int l=0;lrecords = PoiUtil.importXLS();
// for (int i=0;i
需要添加的依赖：
org.apache.poi
poi
3.15
org.apache.poi
poi-ooxml
3.15
org.apache.poi
poi-scratchpad
3.15
org.apache.xmlbeans
xmlbeans
2.5.0
