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,rs.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 <last; i++) { HSSFRow row=hs.getRow(i); int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号 int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号 for (int j = firstCellNum; j <lastCellNum; 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文件

<?xml version='1.0' encoding='UTF-8'?><excel code='student' name='学生信息导入'> <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 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>

所需jar包:commons-lang3-3.1.jarjdom.jarpoi-3.11-20141221.jarcommons-io-2.2.jar

java代码:

//准备工作:导入相关jar包commons-lang3-3.1.jar,jdom.jar,poi-3.11-20141221.jarpublic 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<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);//标题居中 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<Element> ths=tr.getChildren('th'); for (int j = 0; j <ths.size(); 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<Element> 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<Element> 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); } }}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持好吧啦网。