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