java實(shí)現(xiàn)Excel的導(dǎo)入導(dǎo)出

更新時(shí)間：2020年06月09日 17:29:45 作者：xiaopengyaonixi

這篇文章主要為大家詳細(xì)介紹了java實(shí)現(xiàn)Excel的導(dǎo)入導(dǎo)出，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

本文實(shí)例為大家分享了java實(shí)現(xiàn)Excel導(dǎo)入導(dǎo)出的具體代碼，供大家參考，具體內(nèi)容如下

一.Excel讀寫技術(shù)

區(qū)別:

二.jxl讀寫基礎(chǔ)代碼

1.從數(shù)據(jù)庫(kù)將數(shù)據(jù)導(dǎo)出到excel表格

public class JxlExcel {
public static void main(String[] args) {
 //創(chuàng)建Excel文件
 String[] title= {"姓名","課程名","分?jǐn)?shù)"};
 File file=new File("f:/sheet1.xls");
 try {
 file.createNewFile();
 //創(chuàng)建工作簿
 WritableWorkbook workbook=Workbook.createWorkbook(file);
 //創(chuàng)建Sheet
 WritableSheet sheet=workbook.createSheet("表格一", 20);
 //第一行設(shè)置列名
 Label label=null;
 for (int i = 0; i < title.length; i++) {
 label=new Label(i, 0, title[i]);//第一個(gè)參數(shù)為列,第二個(gè)為行
 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表格中讀取數(shù)據(jù)

public class JxlRead {
public static void main(String[] args) {
 //創(chuàng)建workbook
 try {
 Workbook workbook=Workbook.getWorkbook(new File("f:/sheet1.xls"));
 //獲取第一個(gè)表格
 Sheet sheet=workbook.getSheet(0);
 //獲取數(shù)據(jù)
 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讀寫基礎(chǔ)代碼

//所需jar包:commons-io-2.2.jar;poi-3.11-20141221.jar
//通過poi進(jìn)行excel導(dǎo)入數(shù)據(jù)
public class PoiExcel {
public static void main(String[] args) throws SQLException {
 String title[]= {"名字","課程","分?jǐn)?shù)"};
 //1.創(chuàng)建Excel工作簿
 HSSFWorkbook workbook=new HSSFWorkbook();
 //2.創(chuàng)建一個(gè)工作表
 HSSFSheet sheet=workbook.createSheet("sheet2");
 //3.創(chuàng)建第一行
 HSSFRow row=sheet.createRow(0);
 HSSFCell cell=null;
 //4.插入第一行數(shù)據(jù)
 for (int i = 0; i < title.length; i++) {
 cell=row.createCell(i);
 cell.setCellValue(title[i]);
 }
 //5.追加數(shù)據(jù)
 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));
 }
 //創(chuàng)建一個(gè)文件,將Excel內(nèi)容存盤
 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表中內(nèi)容讀取
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);
 //獲取第一個(gè)工作表
 HSSFSheet hs=workbook.getSheetAt(0);
 //獲取Sheet的第一個(gè)行號(hào)和最后一個(gè)行號(hào)
 int last=hs.getLastRowNum();
 int first=hs.getFirstRowNum();
 //遍歷獲取單元格里的信息
 for (int i = first; i <last; i++) {
 HSSFRow row=hs.getRow(i);
 int firstCellNum=row.getFirstCellNum();//獲取所在行的第一個(gè)行號(hào)
 int lastCellNum=row.getLastCellNum();//獲取所在行的最后一個(gè)行號(hào)
 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"};
 
 //創(chuàng)建Excel工作簿
 XSSFWorkbook workbook = new XSSFWorkbook();
 //創(chuàng)建一個(gè)工作表sheet
 Sheet sheet = workbook.createSheet();
 //創(chuàng)建第一行
 Row row = sheet.createRow(0);
 Cell cell = null;
 //插入第一行數(shù)據(jù) id,name,sex
 for (int i = 0; i < title.length; i++) {
 cell = row.createCell(i);
 cell.setCellValue(title[i]);
 }
 //追加數(shù)據(jù)
 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("男");
 }
 //創(chuàng)建一個(gè)文件
 File file = new File("e:/poi_test.xlsx");
 try {
 file.createNewFile();
 //將Excel內(nèi)容存盤
 FileOutputStream stream = FileUtils.openOutputStream(file);
 workbook.write(stream);
 stream.close();
 } catch (IOException e) {
 e.printStackTrace();
 }
 
 }
 
}

四.定制導(dǎo)入模板

1.首先準(zhǔn)備好模板的.xml文件,然后導(dǎo)入所需的jar包

例子:student.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<excel id="student" code="student" name="學(xué)生信息導(dǎo)入">
 <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="學(xué)生信息導(dǎo)入" />
 </tr>
 </title>
 <thead>
 <tr height="16px">
 <th value="編號(hào)" />
 <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" /><!--用戶編號(hào) -->
 <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代碼:

//準(zhǔn)備工作:導(dǎo)入相關(guān)jar包c(diǎn)ommons-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);
 //創(chuàng)建Excel
 HSSFWorkbook workbook=new HSSFWorkbook();
 //創(chuàng)建表格
 HSSFSheet sheet=workbook.createSheet("sheet0");
 //獲取Xml文件的根節(jié)點(diǎn)
 Element root=document.getRootElement();
 //獲取模板名稱
 String tempName=root.getAttributeValue("name");
 //設(shè)置列寬
 Element colgroup=root.getChild("colgroup");
 setColumnWidth(sheet,colgroup);
 //設(shè)置標(biāo)題
 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();//創(chuàng)建單元格格式
 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//標(biāo)題居中
 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;
 //設(shè)置字體
 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++;
 
 }
 //設(shè)置表頭
 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++;
 }
 
 //設(shè)置數(shù)據(jù)區(qū)域樣式
 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導(dǎo)入模板
 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();
 //加載下拉列表內(nèi)容
 DVConstraint constraint = 
 DVConstraint.createExplicitListConstraint(enumValue.split(","));
 //數(shù)據(jù)有效性對(duì)象
 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的節(jié)點(diǎn)
 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, "");//數(shù)值
 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);
 }//對(duì)單位進(jìn)行判斷
 sheet.setColumnWidth(i, v);
 }
 
}
}

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: