java实现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();
 }
}
 
}

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

//所需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();
 }
}
}

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

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