Excel解析的几种实现方式
POI
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能，功能强大，使用HSSF就可实现读写功能。
HSSF是Horrible SpreadSheet Format的缩写，即 “讨厌的电子表格格式”。通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。
- HSSF - 读写Microsoft Excel格式档案的功能,适用于97~03的.xls
- XSSF - 读写Microsoft Excel OOXML格式档案的功能，适用于07以上版本。
- HWPF - 读写Microsoft Word格式档案的功能
- HSLF - 读写Microsoft PowerPoint格式档案的功能
- HDGF - 读写Microsoft Visio格式档案的功能
iText
通过iText不仅可以生成PDF或rtf的文档，而且可以将XML、Html文件转化为PDF文件。
下载iText.jar文件之后，只需要在系统的CLASSPATH中加入iText.jar的路径，在程序中就可以使用iText类库了。
JXL
Java Excel是一开放源码项目，可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。比POI操作简单。还可以设置：字体、颜色、背景、合并单元格等
POI、JXL对比
POI：效率高；操作相对复杂；支持公式，宏，图像图表，一些企业应用上非常实用；能够修饰单元格属性；支持字体、数字、日期操作。
JXL：效率低；操作简单；部分支持；能够修饰单元格属性，格式支持不如POI强大；支持字体、数字、日期操作。
FastExcel
FastExcel是一个采用纯Java开发的excel文件读写组件，支持Excel97-2003文件格式。
FastExcel只能读取单元格的字符信息，而其它属性如颜色，字体等就不支持了，因此FastExcel只需很小的内存。
实现原理
Excel实战之JXL创建
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class TestJXLExcel {
public static void main(String[] args) {
String[] title = {"id", "name", "sex"};
// 创建Excel文件
File file = new File("F:\\jxl_test.xls");
if (file.exists()) {
file.delete();
}
try {
file.createNewFile();
// 创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(file);
// 创建sheet
WritableSheet sheet = workbook.createSheet("sheet1", 0);
Label label = null;
// 第一行设置列名
for (int i = 0; i < title.length; i++) {
label = new Label(i, 0, title[i]);// 列，行，列名
sheet.addCell(label);
}
// 追加数据
for (int i = 1; i < 10; i++) {
label = new Label(0, i, "a" + i);// 第一列，第i行，名称
sheet.addCell(label);
label = new Label(1, i, "user" + i);
sheet.addCell(label);
label = new Label(2, i, "男" + i);
sheet.addCell(label);
}
workbook.write();
workbook.close();
System.out.println("文件生成");
} catch (Exception e) {
e.printStackTrace();
}
}
}
Excel实战之JXL解析
import java.io.File;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class TestJxlReadExcel {
public static void main(String[] args) {
try {
File file = new File("F:\\jxl_test.xls");
//创建workbook
Workbook workbook = Workbook.getWorkbook(file);
//获取工作表sheet
Sheet sheet = workbook.getSheet(0);
//获取数据
for (int i = 0; i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
System.out.print(cell.getContents() + " ");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}Excel实战之POI创建
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class TestPOIExportExcel {
public static void main(String[] args) {
String[] title = {"id", "name", "sex"};
//创建Excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个工作表sheet
HSSFSheet sheet = workbook.createSheet();
//创建第一行
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
//插入第一行数据id name sex
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
//追加数据
for (int i = 1; i <= 10; i++) {
HSSFRow nextRow = sheet.createRow(i);
HSSFCell cell2 = nextRow.createCell(0);//创建第一个单元格
cell2.setCellValue("a" + i);//为第一个单元格赋值
cell2 = nextRow.createCell(1);
cell2.setCellValue("user" + i);
cell2 = nextRow.createCell(2);
cell2.setCellValue("男");
}
try {
//创建文件
File file = new File("F:/poi_test.xls");
if (file.exists()) {
file.delete();
}
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
System.out.println("文件创建成功");
} catch (IOException e) {
e.printStackTrace();
}
}
}Excel实战之POI解析
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class TestPoiReadExcel {
public static void main(String[] args) {
try {
//需要解析的Excel文件
File file = new File("F:/poi_test.xls");
//创建Excel读取文件
FileInputStream stream = FileUtils.openInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(stream);
//获取第一个工作表
// HSSFSheet sheet = workbook.getSheet("Sheet0");
//读取默认第一个工作表sheet
HSSFSheet sheet = workbook.getSheetAt(0);
// int firstRowNum = 0;
//获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
HSSFRow row = sheet.getRow(i);
//获取当前行最后单元格列号
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
HSSFCell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.print(value + " ");
}
System.out.println();
}
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}Excel实战之XSSF创建高版本Excel
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.commons.io.FileUtils;
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.xssf.usermodel.XSSFWorkbook;
public class TestPOIExportExcel2 {
public static void main(String[] args) {
String[] title = {"id", "name", "sex"};
//创建Excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建一个工作表sheet
Sheet sheet = workbook.createSheet();
//创建第一行
Row row = sheet.createRow(0);
Cell cell = null;
//插入第一行数据id name sex
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
//追加数据
for (int i = 1; i <= 10; i++) {
Row nextRow = sheet.createRow(i);
Cell cell2 = nextRow.createCell(0);//创建第一个单元格
cell2.setCellValue("a" + i);//为第一个单元格赋值
cell2 = nextRow.createCell(1);
cell2.setCellValue("user" + i);
cell2 = nextRow.createCell(2);
cell2.setCellValue("男");
}
try {
//创建文件
File file = new File("F:/poi_test.xlsx");
if (file.exists()) {
file.delete();
}
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
System.out.println("文件创建成功");
} catch (IOException e) {
e.printStackTrace();
}
}
}导入模板定制
生成导入模板-生成规则
- 确定模板列
- 定义标题（合并单元格）
- 定义列名
- 定义数据区域单元格样式
<?xml version="1.0" encoding="UTF-8"?>
<excel id="student" code="student" name="学生信息导入">
<!-- Excel列，列宽 -->
<colgroup>
<col index="A" width='17em'></col>
<col index="B" width='17em'></col>
<col index="C" width='17em'></col>
<col index="D" width='17em'></col>
<col index="E" width='17em'></col>
<col index="F" width='17em'></col>
</colgroup>
<!-- 文件标题 -->
<title>
<tr height="16px">
<!-- 从哪一行到哪一列合并单元格 -->
<td rowspan="1" colspan="6" value="学生信息导入" />
</tr>
</title>
<!-- 定义表头 -->
<thead>
<tr height="16px">
<th value="编号" />
<th value="姓名" />
<th value="年龄" />
<th value="性别" />
<th value="出生日期" />
<th value=" 爱好" />
</tr>
</thead>
<!-- 定义数据区域样式 -->
<tbody>
<tr height="16px" firstrow="2" firstcol="0" repeat="5">
<td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->
<td type="string" isnullable="false" maxlength="50" /><!--姓名 -->
<td type="numeric" format="##0" isnullable="false" /><!--年龄 -->
<td type="enum" format="男,女" isnullable="true" /><!--性别 -->
<td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->
<td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->
</tr>
</tbody>
</excel>生成导入模板-设置列宽、标题、表头、表体
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jdom.Attribute;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
public class TestCreateTemplate {
public static void main(String[] args) {
String dir = System.getProperty("user.dir")
+ "/src/com/lijy/EXCEL/student.xml";
File file = new File(dir);
SAXBuilder builder = new SAXBuilder();
try {
// 解析xml文件
Document parse = builder.build(file);
// 创建Excel
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = workbook.createSheet("Sheet0");
// 获取xml文件根节点
Element root = parse.getRootElement();
// 获取模板名称
String templateName = root.getAttributeValue("name");
int rownum = 0;
int column = 0;
// 设置列宽
Element colgroup = root.getChild("colgroup");
setColumnWidth(sheet, colgroup);
// 设置标题
Element title = root.getChild("title");
List<Element> trs = title.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
List<Element> tds = tr.getChildren("td");
HSSFRow row = sheet.createRow(rownum);
// 设置单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置字体
HSSFFont font = workbook.createFont();
font.setFontName("仿宋_GB2312");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
// font.setFontHeight((short) 12);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
Attribute rowSpan = td.getAttribute("rowspan");
Attribute colSpan = td.getAttribute("colspan");
Attribute value = td.getAttribute("value");
if (value != null) {
String val = value.getValue();
cell.setCellValue(val);
int tspan = rowSpan.getIntValue() - 1;
int cspan = colSpan.getIntValue() - 1;
cell.setCellStyle(cellStyle);
// 合并单元格居中
sheet.addMergedRegion(new CellRangeAddress(tspan,
tspan, 0, cspan));
}
}
rownum++;
}
// 设置表头
Element thead = root.getChild("thead");
trs = thead.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
HSSFRow row = sheet.createRow(rownum);
List<Element> ths = tr.getChildren("th");
for (column = 0; column < ths.size(); column++) {
Element th = ths.get(column);
Attribute valueAttribute = th.getAttribute("value");
HSSFCell cell = row.createCell(column);
if (valueAttribute != null) {
String value = valueAttribute.getValue();
cell.setCellValue(value);
}
}
rownum++;
}
// 设置区域样式
Element tbody = root.getChild("tbody");
Element tr = tbody.getChild("tr");
int repeat = tr.getAttribute("repeat").getIntValue();
List<Element> tds = tr.getChildren("td");
for (int i = 0; i < repeat; i++) {
HSSFRow row = sheet.createRow(rownum);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
setType(workbook, cell, td);
}
rownum++;
}
//生成Excel导入模板
File tempFile = new File("F:/" + templateName + ".xls");
tempFile.delete();
tempFile.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(tempFile);
workbook.write(stream);
stream.close();
} catch (JDOMException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 设置单元格样式
*
* @param workbook
* @param cell
* @param td
*/
private static void setType(HSSFWorkbook workbook, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = workbook.createDataFormat();
HSSFCellStyle cellStyle = workbook.createCellStyle();
if ("NUMERIC".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNotBlank(formatValue)
? formatValue
: "#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
} else if ("STRING".equalsIgnoreCase(type)) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
} else if ("DATE".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
} else if ("ENUM".equalsIgnoreCase(type)) {
CellRangeAddressList regions = new CellRangeAddressList(cell
.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(),
cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
//加载下拉列表内容
DVConstraint constraint =
DVConstraint.createExplicitListConstraint(enumValue.split(","));
//数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
workbook.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
/**
* 设置列宽
*
* @param sheet
* @param colgroup
*/
private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
List<Element> cols = colgroup.getChildren("col");
for (int i = 0; i < cols.size(); i++) {
Element col = cols.get(i);
String width = col.getAttributeValue("width");
String unit = width.replaceAll("[0-9,\\.]", "");
System.out.println(unit);
String value = width.replaceAll(unit, "");
System.out.println(value);
int v = 0;
if (StringUtils.isBlank(unit) || "px".endsWith(unit)) {
v = Math.round(Float.parseFloat(value) * 37F);
} else if ("em".endsWith(unit)) {
v = Math.round(Float.parseFloat(value) * 267.5F);
}
sheet.setColumnWidth(i, v);
}
}
}
976
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
