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文件

¨C7C ¨C8C ¨C9C ¨C10C ¨C11C ¨C12C

所需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 trs=title.getChildren("tr");
for (int i = 0; i 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 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);
}

}
}