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