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 id="student" 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 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>
所需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<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); } } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。
赞 (0)