Java 制作一个Excel工具类 - IT评测·应用市场-qidao123.com技术社区

package test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class Main {
public static void main(String[] args) throws IOException {
Main main = new Main("C:/Users/June/Desktop/a.xlsx");
main.readDocuments();
}
ZipInputStream zis;
public Main(String pathname) throws FileNotFoundException {
zis = new ZipInputStream(new FileInputStream(new File(pathname)));
}
public void readDocuments() throws IOException {
try {
ZipEntry zipEntry;
while((zipEntry = zis.getNextEntry())!=null){
String entryName = zipEntry.getName();//每个ZipEntry就是一个文件
if(entryName.startsWith("xl/media")){
readMedia(entryName);
}else{
readDocument(entryName);
}
}
} finally {
if(zis!=null){
zis.close();
}
}
}
//处理XML文件
private void readDocument(String entryName) {
Element root = parse(zis);//XML文件根元素
//处理逻辑
}
//处理媒体文件
private void readMedia(String entryName) {
}
// 1. 创建DOM解析器工厂
private static final DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
static {
try {
// 禁用外部实体（安全防护）
factory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
factory.setFeature("http://xml.org/sax/features/external-general-entities", false);
factory.setFeature("http://xml.org/sax/features/external-parameter-entities", false);
} catch (Exception ignored) {
}
}
private static Element parse(InputStream is) {
try {
// 2. 创建DOM解析器
DocumentBuilder builder = factory.newDocumentBuilder();
// 3. 解析XML文件
Document doc = builder.parse(is);
// 4. 标准化文档结构
doc.getDocumentElement().normalize();
// 5. 获取根元素
return doc.getDocumentElement();
} catch (Exception ignored) {
}
return null;
}
}

复制代码

package test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
public class Main {
public static void main(String[] args) throws IOException {
Main main = new Main("C:/Users/June/Desktop/a.xlsx");
main.write();
}
ZipOutputStream zos;
public Main(String pathname) throws IOException {
File f = new File(pathname);
if(!f.getParentFile().exists()){
f.getParentFile().mkdirs();
}
if(!f.exists()){
f.createNewFile();
}
zos = new ZipOutputStream(new FileOutputStream(f));
}
public void write() throws IOException {
try {
zos.putNextEntry(new ZipEntry("_rels/.rels"));
//输出.rels文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.putNextEntry(new ZipEntry("xl/workbook.xml"));
//输出workbook.xml文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.putNextEntry(new ZipEntry("xl/sharedStrings.xml"));
//输出sharedStrings.xml文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.putNextEntry(new ZipEntry("xl/_rels/workbook.xml.rels"));
//输出workbook.xml.rels文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.putNextEntry(new ZipEntry("xl/styles.xml"));
//输出styles.xml文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.putNextEntry(new ZipEntry("xl/worksheets/sheet1.xml"));
//输出sheet1.xml文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.putNextEntry(new ZipEntry("[Content_Types].xml"));
//输出[Content_Types].xml文件结构
zos.write("<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>".getBytes());
//.....
zos.flush();
} finally {
if(zos != null){
zos.closeEntry();
zos.close();
}
}
}
}

复制代码

package test;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.List;
import june.zero.excel.Excel;
import june.zero.excel.Excel.ExcelStyle;
import june.zero.excel.Excel.ExcelWriter;
import june.zero.excel.xl.Styles.Alignment;
import june.zero.excel.xl.Styles.Borders.Border;
import june.zero.excel.xl.Styles.Fills.Fill;
import june.zero.excel.xl.Styles.Fonts.Font;
import june.zero.excel.xl.worksheets.Worksheet;
import june.zero.excel.xl.worksheets.Worksheet.Cols;
import june.zero.excel.xl.worksheets.Worksheet.MergeCells;
import june.zero.excel.xl.worksheets.Worksheet.SheetData;
import june.zero.excel.xl.worksheets.Worksheet.SheetFormatPr;
public class ExcelTest {
public static void main(String[] args) throws IOException {
File f = new File("C:/Users/June/Desktop/a.xlsx");
if(!f.exists()) f.createNewFile();
OutputStream os = Files.newOutputStream(f.toPath());
ExcelWriter excel = Excel.createExcelWriter(os);
Worksheet sheet1 = excel.createWorksheet(null);
Cols cols = sheet1.getCols();
cols.addCol(1,3,27.0);//1到3列，列宽都是27.0
MergeCells mergeCells = sheet1.getMergeCells();
mergeCells.addMergeCell("B", 4, "C", 5);//合并B4-C5
SheetFormatPr sheetFormatPr = sheet1.getSheetFormatPr();
sheetFormatPr.setDefaultColWidth(9);//默认所有列宽
sheetFormatPr.setDefaultRowHeight(90);//默认所有行高
SheetData sheet1Data = sheet1.getSheetData();
sheet1Data.setValue(0, 0, "111");//第一行第一列的值为111
sheet1Data.setRowHeight(0, 72.0);//第一行的行高为72.0
List<List<String>> dataList = new ArrayList<List<String>>();
List<String> r2 = new ArrayList<String>();
r2.add("A2");
r2.add("B2");
r2.add("C2");
r2.add("D2");
dataList.add(r2);
List<String> r3 = new ArrayList<String>();
r3.add("A3");
r3.add("B3");
r3.add("C3");
dataList.add(r3);
// sheet1Data.setValue("A", 2, dataList);//从A2开始赋值
sheet1Data.setValue("A2", dataList);//从A2开始赋值
ExcelStyle cs = new ExcelStyle();
Font font = new Font();
font.setB(true);//字体粗体
font.setSz(13);//字体大小
font.setI(true);//字体斜体
font.setName("黑体");//字体名称
font.setRgb("ed6e19");//字体颜色
cs.setFont(font);//字体样式
Fill fill = new Fill();//填充
fill.setPatternType(Fill.solid);
fill.setBgColor("30c7c9");//背景色
fill.setFgColor("30c7c9");//前景色
cs.setFill(fill);//填充色
Border border = new Border();
border.setBorder("000000", Border.thin);//黑色实线
cs.setBorder(border);//边框
Alignment alignment = new Alignment();
alignment.setHorizontal(Alignment.center);//水平居中
alignment.setVertical(Alignment.center);//垂直居中
alignment.setWrapText(true);//自动换行
cs.setAlignment(alignment);
sheet1Data.setStyle(1, 0, cs);
sheet1Data.setStyle(1, 1, cs);
excel.write();
}
}

复制代码