java实现Excel的导入导出

java实现Excel的导入导出

本文实例为大家分享了java实现Excel导入导出的具体代码，供大家参考，具体内容如下

一.Excel读写技术

区别:

二.jxl读写基础代码

1.从数据库将数据导出到excel表格

public class JxlExcel {

public static void main(String[] args) {

//创建Excel文件

String[] title= {"姓名","课程名","分数"};

File file=new File("f:/sheet1.xls");

try {

file.createNewFile();

//创建工作簿

WritableWorkbook workbook=Workbook.createWorkbook(file);

//创建Sheet

WritableSheet sheet=workbook.createSheet("表格一", 20);

//第一行设置列名

Label label=null;

for (int i = 0; i < title.length; i++) {

label=new Label(i, 0, title[i]);//第一个参数为列,第二个为行

sheet.addCell(label);

}

Data data=new Data();

ResultSet rs=data.getString();

while(rs.next()) {

System.out.println(rs.getString(1));

label=new Label(0,rs.getRow(),rs.getString(1));

sheet.addCell(label);

label=new Label(1,dmDwVZxVkrs.getRow(),rs.getString(2));

sheet.addCell(label);

label=new Label(2,rs.getRow(),rs.getString(3));

sheet.addCell(label);

}

workbook.write();

workbook.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

2.从Excel表格中读取数据

public class JxlRead {

public static void main(String[] args) {

//创建workbook

try {

Workbook workbook=Workbook.getWorkbook(new File("f:/sheet1.xls"));

//获取第一个表格

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) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

三.Poi读写基础代码

//所需jar包:commons-io-2.2.jar;poi-3.11-20141221.jar

//通过poi进行excel导入数据

public class PoiExcel {

public static void main(String[] args) throws SQLException {

String title[]= {"名字","课程","分数"};

//1.创建Excel工作簿

HSSFWorkbook workbook=new HSSFWorkbook();

//2.创建一个工作表

HSSFSheet sheet=workbook.createSheet("sheet2");

//3.创建第一行

HSSFRow row=sheet.createRow(0);

HSSFCell cell=null;

//4.插入第一行数据

for (int i = 0; i < title.length; i++) {

cell=row.createCell(i);

cell.setCellValue(title[i]);

}

//5.追加数据

Data data=new Data();

ResultSet rs=data.getString();

while(rs.next()) {

HSSFRow row2=sheet.createRow(rs.getRow());

HSSFCell cell2=row2.createCell(0);

cell2.setCellValue(rs.getString(1));

cell2=row2.createCell(1);

cell2.setCellValue(rs.getString(2));

cell2=row2.createCell(2);

cell2.setCellValue(rs.getString(3));

}

//创建一个文件,将Excel内容存盘

File file=new File("e:/sheet2.xls");

try {

file.createNewFile();

FileOutputStream stream=FileUtils.openOutputStream(file);

workbook.write(stream);

stream.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

//将Excel表中内容读取

public class PoiRead {

public static void main(String[] args) {

//需要解析的Excel文件

File file=new File("e:/sheet2.xls");

try {

//获取工作簿

FileInputStream fs=FileUtils.openInputStream(file);

HSSFWorkbook workbook=new HSSFWorkbook(fs);

//获取第一个工作表

HSSFSheet hs=workbook.getSheetAt(0);

//获取Sheet的第一个行号和最后一个行号

int last=hs.getLastRowNum();

int first=hs.getFirstRowNum();

//遍历获取单元格里的信息

for (int i = first; i

HSSFRow row=hs.getRow(i);

int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号

int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号

for (int j = firstCellNum; j

HSSFCell cell=row.getCell(j);

String value=cell.getStringCellValue();

System.out.print(value+" ");

}

System.out.println();

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

如果Excel版本过高则需要改写用XSSF

public class PoiExpExcel2 {

/**

* POI生成Excel文件

* @author David

* @param args

*/

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("男");

}

//创建一个文件

File file = new File("e:/poi_test.xlsx");

try {

file.createNewFile();

//将Excel内容存盘

FileOutputStream stream = FileUtils.openOutputStream(file);

workbook.write(stream);

stream.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

四.定制导入模板

1.首先准备好模板的.xml文件,然后导入所需的jar包

例子:student.xml文件

HSSFRow row=hs.getRow(i);

int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号

int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号

for (int j = firstCellNum; j

HSSFCell cell=row.getCell(j);

String value=cell.getStringCellValue();

System.out.print(value+" ");

}

System.out.println();

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

如果Excel版本过高则需要改写用XSSF

public class PoiExpExcel2 {

/**

* POI生成Excel文件

* @author David

* @param args

*/

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("男");

}

//创建一个文件

File file = new File("e:/poi_test.xlsx");

try {

file.createNewFile();

//将Excel内容存盘

FileOutputStream stream = FileUtils.openOutputStream(file);

workbook.write(stream);

stream.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

四.定制导入模板

1.首先准备好模板的.xml文件,然后导入所需的jar包

例子:student.xml文件

HSSFCell cell=row.getCell(j);

String value=cell.getStringCellValue();

System.out.print(value+" ");

}

System.out.println();

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

如果Excel版本过高则需要改写用XSSF

public class PoiExpExcel2 {

/**

* POI生成Excel文件

* @author David

* @param args

*/

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("男");

}

//创建一个文件

File file = new File("e:/poi_test.xlsx");

try {

file.createNewFile();

//将Excel内容存盘

FileOutputStream stream = FileUtils.openOutputStream(file);

workbook.write(stream);

stream.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

四.定制导入模板

1.首先准备好模板的.xml文件,然后导入所需的jar包

例子:student.xml文件

所需jar包:

commons-lang3-3.1.jar

jdom.jar

poi-3.11-20141221.jar

commons-io-2.2.jar

java代码:

//准备工作:导入相关jar包commons-lang3-3.1.jar,jdom.jar,poi-3.11-20141221.jar

public class CreateTemp {

public static void main(String[] args) {

//获取解析Xml路径

String path=System.getProperty("user.dir")+"/student.xml";

File file=new File(path);

SAXBuilder builder=new SAXBuilder();

//解析xml文件

try {

Document document=builder.build(file);

//创建Excel

HSSFWorkbook workbook=new HSSFWorkbook();

//创建表格

HSSFSheet sheet=workbook.createSheet("sheet0");

//获取Xml文件的根节点

Element root=document.getRootElement();

//获取模板名称

String tempName=root.getAttributeValue("name");

//设置列宽

Element colgroup=root.getChild("colgroup");

setColumnWidth(sheet,colgroup);

//设置标题

int rownum = 0;

int column = 0;

Element title=root.getChild("title");

List trs=title.getChildren("tr");

for (int i = 0; i

Element tr=trs.get(i);

List tds=tr.getChildren("td");

HSSFRow row=sheet.createRow(rownum);

HSSFCellStyle cellStyle=workbook.createCellStyle();//创建单元格格式

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//标题居中

for (int j = 0; j < tds.size(); j++) {

Element td=tds.get(j);

HSSFCell cell=row.createCell(j);

Attribute rowspan=td.getAttribute("rowspan");

Attribute colspan=td.getAttribute("colspan");

Attribute value=td.getAttribute("value");

if (value!=null) {

String content=value.getValue();

cell.setCellValue(content);

int rspan=rowspan.getIntValue()-1;

int cspan=colspan.getIntValue()-1;

//设置字体

HSSFFont font=workbook.createFont();

font.setFontName("仿宋_GB2312");

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗

// font.setFontHeight((short)12);

font.setFontHeightInPoints((short)12);

cellStyle.setFont(font);

cell.setCellStyle(cellStyle);

//合并单元格居中

sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 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 ths=tr.getChildren("th");

for (int http://j = 0; j

Element th=ths.get(j);

HSSFCell cell=row.createCell(j);

Attribute value=th.getAttribute("value");

if (value!=null) {

String content=value.getValue();

cell.setCellValue(content);

}

}

rownum++;

}

//设置数据区域样式

Element tbody = root.getChild("tbody");

Element tr=tbody.getChild("tr");

int repeat=tr.getAttribute("repeat").getIntValue();

List tds=tr.getChildren("td");

for (int i = 0; i < repeat; i++) {

HSSFRow row=sheet.createRow(rownum);

for (int j = 0; j < tds.size(); j++) {

Element td=tds.get(j);

HSSFCell cell=row.createCell(j);

setType(workbook,cell,td);

}

}

rownum++;

//生成Excel导入模板

File tempFile=new File("e:/"+tempName+".xls");

tempFile.delete();

tempFile.createNewFile();

FileOutputStream fos=FileUtils.openOutputStream(tempFile);

workbook.write(fos);

fos.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

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

}

private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {

List cols=colgroup.getChildren("col");//获取col的节点

for (int i = 0; i < cols.size(); i++) {

Element col=cols.get(i);

Attribute width=col.getAttribute("width");//获取每列中的width属性

String unit = width.getValue().replaceAll("[0-9,\\.]", "");//单位

String value = width.getValue().replaceAll(unit, "");//数值

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

}

}

}

Element tr=trs.get(i);

List tds=tr.getChildren("td");

HSSFRow row=sheet.createRow(rownum);

HSSFCellStyle cellStyle=workbook.createCellStyle();//创建单元格格式

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//标题居中

for (int j = 0; j < tds.size(); j++) {

Element td=tds.get(j);

HSSFCell cell=row.createCell(j);

Attribute rowspan=td.getAttribute("rowspan");

Attribute colspan=td.getAttribute("colspan");

Attribute value=td.getAttribute("value");

if (value!=null) {

String content=value.getValue();

cell.setCellValue(content);

int rspan=rowspan.getIntValue()-1;

int cspan=colspan.getIntValue()-1;

//设置字体

HSSFFont font=workbook.createFont();

font.setFontName("仿宋_GB2312");

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗

// font.setFontHeight((short)12);

font.setFontHeightInPoints((short)12);

cellStyle.setFont(font);

cell.setCellStyle(cellStyle);

//合并单元格居中

sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 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 ths=tr.getChildren("th");

for (int http://j = 0; j

Element th=ths.get(j);

HSSFCell cell=row.createCell(j);

Attribute value=th.getAttribute("value");

if (value!=null) {

String content=value.getValue();

cell.setCellValue(content);

}

}

rownum++;

}

//设置数据区域样式

Element tbody = root.getChild("tbody");

Element tr=tbody.getChild("tr");

int repeat=tr.getAttribute("repeat").getIntValue();

List tds=tr.getChildren("td");

for (int i = 0; i < repeat; i++) {

HSSFRow row=sheet.createRow(rownum);

for (int j = 0; j < tds.size(); j++) {

Element td=tds.get(j);

HSSFCell cell=row.createCell(j);

setType(workbook,cell,td);

}

}

rownum++;

//生成Excel导入模板

File tempFile=new File("e:/"+tempName+".xls");

tempFile.delete();

tempFile.createNewFile();

FileOutputStream fos=FileUtils.openOutputStream(tempFile);

workbook.write(fos);

fos.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

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

}

private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {

List cols=colgroup.getChildren("col");//获取col的节点

for (int i = 0; i < cols.size(); i++) {

Element col=cols.get(i);

Attribute width=col.getAttribute("width");//获取每列中的width属性

String unit = width.getValue().replaceAll("[0-9,\\.]", "");//单位

String value = width.getValue().replaceAll(unit, "");//数值

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

}

}

}

Element th=ths.get(j);

HSSFCell cell=row.createCell(j);

Attribute value=th.getAttribute("value");

if (value!=null) {

String content=value.getValue();

cell.setCellValue(content);

}

}

rownum++;

}

//设置数据区域样式

Element tbody = root.getChild("tbody");

Element tr=tbody.getChild("tr");

int repeat=tr.getAttribute("repeat").getIntValue();

List tds=tr.getChildren("td");

for (int i = 0; i < repeat; i++) {

HSSFRow row=sheet.createRow(rownum);

for (int j = 0; j < tds.size(); j++) {

Element td=tds.get(j);

HSSFCell cell=row.createCell(j);

setType(workbook,cell,td);

}

}

rownum++;

//生成Excel导入模板

File tempFile=new File("e:/"+tempName+".xls");

tempFile.delete();

tempFile.createNewFile();

FileOutputStream fos=FileUtils.openOutputStream(tempFile);

workbook.write(fos);

fos.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

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

}

private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {

List cols=colgroup.getChildren("col");//获取col的节点

for (int i = 0; i < cols.size(); i++) {

Element col=cols.get(i);

Attribute width=col.getAttribute("width");//获取每列中的width属性

String unit = width.getValue().replaceAll("[0-9,\\.]", "");//单位

String value = width.getValue().replaceAll(unit, "");//数值

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

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。