Java实现Excel导入导出

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);// 创建sheetWritableSheet 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");//创建workbookWorkbook workbook = Workbook.getWorkbook(file);//获取工作表sheetSheet 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();//创建一个工作表sheetHSSFSheet sheet = workbook.createSheet();//创建第一行HSSFRow row = sheet.createRow(0);HSSFCell cell = null;//插入第一行数据id name sexfor (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");//读取默认第一个工作表sheetHSSFSheet 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();//创建一个工作表sheetSheet sheet = workbook.createSheet();//创建第一行Row row = sheet.createRow(0);Cell cell = null;//插入第一行数据id name sexfor (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解析技术，确定模板样式

• 确定模板列
• 定义标题（合并单元格）    
• 定义列名
• 定义数据区域单元格样式

<?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);// 创建ExcelHSSFWorkbook workbook = new HSSFWorkbook();// 创建sheetHSSFSheet 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 blocke.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);}}}

参考

http://www.imooc.com/