Excel的创建与解析

import java.io.File;import java.io.IOException;import jxl.Workbook;import jxl.write.Label;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;/* * jxl创建excel * 相关jar包jxl.jar */public class JxlExpExcel {public static void main(String[] args) throws Exception {String[] title={"id","name","sex"};//创建excelFile file = new File("D:\\excel.xls");file.createNewFile();WritableWorkbook workBook = Workbook.createWorkbook(file);//创建sheetWritableSheet sheet = workBook.createSheet("sheet1", 0);Label label = null;for(int i=0;i<title.length;i++){label = new Label(i,0,title[i]);//第几列，第几行，数据sheet.addCell(label);}for(int j=1;j<10;j++){label = new Label(0,j,"id"+j);sheet.addCell(label);label = new Label(1,j,"name"+j);sheet.addCell(label);label = new Label(2,j,"sex"+j);sheet.addCell(label);}workBook.write();workBook.close();}}

import java.io.File;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;/* * jxl解析excel */public class JxlReadExcel {public static void main(String[] args) throws Exception {//创建workbookWorkbook  workbook = Workbook.getWorkbook(new File("D:\\excel.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();}}}

import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/* * POI创建excel,需要poi.jar包 */public class POIExcel {public static void main(String[] args) throws IOException {String[] title={"name","id","sex"};//创建excelHSSFWorkbook workbook = new HSSFWorkbook();//创建一个工作表HSSFSheet sheet = workbook.createSheet();//创建第一行HSSFRow row = sheet.createRow(0);HSSFCell cell = null;//插入第一行数据for(int i=0;i<title.length;i++){cell = row.createCell(i);cell.setCellValue(title[i]);}//追加数据for(int j=1;j<10;j++){HSSFRow row1 = sheet.createRow(j);//行cell = row1.createCell(0);//列cell.setCellValue("name"+j);cell = row1.createCell(1);cell.setCellValue("id"+j);cell = row1.createCell(2);cell.setCellValue("sex"+j);}//写入文件File file = new File("D:/s.xls");file.createNewFile();FileOutputStream os = new FileOutputStream(file);workbook.write(os);workbook.close();os.close();}}

import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/* * POI解析excel */public class POIReadExcel {public static void main(String[] args) throws Exception{//创建excel，读取文件内容HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("D:/s.xls")));//读取一个sheet页//HSSFSheet sheet = workbook.getSheet("sheet0");HSSFSheet sheet = workbook.getSheetAt(0);int firstRowNum=0;//第一行//获取最后一行int lastRowNum = sheet.getLastRowNum();//不算第一行标题System.out.println(lastRowNum);//读取数据，获取每一行for(int i=firstRowNum;i<lastRowNum+1;i++){HSSFRow row = sheet.getRow(i);//获取最后一个单元格的值int lastCellNum = row.getLastCellNum();//获取每一个单元格for(int j = 0;j<lastCellNum;j++){HSSFCell cell = row.getCell(j);System.out.print(cell.getStringCellValue()+" ");}System.out.println();}}}

创建Excel模板文件：

import java.io.File;import java.io.FileOutputStream;import java.util.List;import org.apache.commons.io.FileUtils;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import org.jdom.Attribute;import org.jdom.Document;import org.jdom.Element;import org.jdom.input.SAXBuilder;public class CreateTemplate {/** * 创建模板文件 * @author David * @param args */public static void main(String[] args) {//获取解析xml文件路径String path = System.getProperty("user.dir") + "/bin/student.xml";//src下的xml文件File file = new File(path);//放到服务器里可以放入webapps下/*String path = req.getServletContext().getRealPath("/").replace("\\", "/");  String filename = req.getParameter("filename");File file = new File(path +"xml"+"\\"+ filename);*/SAXBuilder builder = new SAXBuilder();try {//解析xml文件Document parse = builder.build(file);//创建ExcelHSSFWorkbook wb = new HSSFWorkbook();//创建sheetHSSFSheet sheet = wb.createSheet("Sheet0");//获取xml文件跟节点Element root = parse.getRootElement();//获取模板名称String templateName = root.getAttribute("name").getValue();int rownum = 0;int column = 0;//设置列宽Element colgroup = root.getChild("colgroup");setColumnWidth(sheet,colgroup);//设置标题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 = wb.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);for(column = 0;column <tds.size();column ++){Element td = tds.get(column);HSSFCell cell = row.createCell(column);Attribute rowSpan = td.getAttribute("rowspan");Attribute colSpan = td.getAttribute("colspan");Attribute value = td.getAttribute("value");if(value !=null){String val = value.getValue();cell.setCellValue(val);int rspan = rowSpan.getIntValue() - 1;int cspan = colSpan.getIntValue() -1;//设置字体HSSFFont font = wb.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(column = 0;column < ths.size();column++){Element th = ths.get(column);Attribute valueAttr = th.getAttribute("value");HSSFCell cell = row.createCell(column);if(valueAttr != null){String value =valueAttr.getValue();cell.setCellValue(value);}}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(column =0 ;column < tds.size();column++){Element td = tds.get(column);HSSFCell cell = row.createCell(column);setType(wb,cell,td);}rownum++;}//生成Excel导入模板File tempFile = new File("D:/" + templateName + ".xls");tempFile.delete();tempFile.createNewFile();FileOutputStream stream = FileUtils.openOutputStream(tempFile);wb.write(stream);stream.close();} catch (Exception e) {e.printStackTrace();}}/** * 测试单元格样式 * @author David * @param wb * @param cell * @param td */private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {Attribute typeAttr = td.getAttribute("type");String type = typeAttr.getValue();HSSFDataFormat format = wb.createDataFormat();HSSFCellStyle cellStyle = wb.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);wb.getSheetAt(0).addValidationData(dataValidation);}cell.setCellStyle(cellStyle);}/** * 设置列宽 * @author David * @param sheet * @param colgroup */private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {List<Element> cols = colgroup.getChildren("col");for (int i = 0; i < cols.size(); i++) {Element col = cols.get(i);Attribute width = col.getAttribute("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);}}}

<?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>