java j2ee poi xsl excel 文档操作 包括上传 下载

package emp; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.sql.ResultSet; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * * @author shiwt * * java读取excel文件 * * 一个Excel文件的层次：Excel文件->工作表->行->单元格 对应到POI中，为：workbook->sheet->row->cell * */ public class EmpPoi { public static String outputFile = "c:\\emp.xls"; public static String fileToBeRead = "c:\\emp.xls"; public void CreateExcel() { try { // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // ---------------------------------------------------------- // 表名样式 HSSFCellStyle titlecellstyle = workbook.createCellStyle(); titlecellstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左对齐 HSSFFont titleFont = workbook.createFont(); titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体 titleFont.setUnderline(HSSFFont.U_SINGLE); // 单下画线 titleFont.setFontName("Arial"); // 字体 titleFont.setFontHeightInPoints((short) 14); // 大小 titlecellstyle.setFont(titleFont); // 表头样式 HSSFCellStyle formTitleStyle = workbook.createCellStyle(); formTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); formTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); formTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); formTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); formTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont formTitleFont = workbook.createFont(); formTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); formTitleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 30); formTitleStyle.setFont(formTitleFont); // 表内容样式 HSSFCellStyle contentStyle = workbook.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont contentFont = workbook.createFont(); contentFont.setFontName("Arial"); contentFont.setFontHeightInPoints((short) 12); contentStyle.setFont(contentFont); // ----------------------------------------------------- // 在Excel工作簿中建一工作表，其名为缺省值 // 如要新建一名为"效益指标"的工作表，其语句为： // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet("empList"); // sheet = workbook.createSheet(); // 在索引0的位置创建行（最顶端的行） HSSFRow row = sheet.createRow((short) 0); // 在索引0的位置创建单元格（左上端） HSSFCell cell = row.createCell((short) 0); // 生成表头 cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("EMPNO"); cell = row.createCell((short) 1); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("ENAME"); cell = row.createCell((short) 2); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("JOB"); cell = row.createCell((short) 3); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("MGR"); cell = row.createCell((short) 4); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("HIREDATE"); cell = row.createCell((short) 5); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("SAL"); cell = row.createCell((short) 6); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("COMM"); cell = row.createCell((short) 7); cell.setCellStyle(formTitleStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("DEPTNO"); EmpDao empdao = new EmpDao(); ResultSet rs = empdao .getList("select EMPNO, ENAME, JOB, MGR,HIREDATE,SAL, nvl(COMM,0), DEPTNO from EMP"); List<EmpPOJO> list = new ArrayList(); int j = 0; while (rs.next()) { row = sheet.createRow((short) j + 1); for (int k = 0; k < 8; k++) { cell = row.createCell((short) k); cell.setCellStyle(contentStyle); cell.setCellValue(new HSSFRichTextString(rs .getString(k + 1))); } j++; } // 新建一输出文件流 File file = new File(outputFile); FileOutputStream fOut = new FileOutputStream(file); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束，关闭文件 fOut.close(); System.out.println("文件生成..."); } catch (Exception e) { e.printStackTrace(); System.out.println("已运行 xlCreate() : " + e); } } /** * * 读取excel，遍历各个小格获取其中信息，并判断其是否是手机号码，并对正确的手机号码进行显示 * * * 注意： 1.sheet， 以0开始，以workbook.getNumberOfSheets()-1结束 2.row， * 以0开始(getFirstRowNum)，以getLastRowNum结束 3.cell， * 以0开始(getFirstCellNum)，以getLastCellNum结束, 结束的数目不知什么原因与显示的长度不同，可能会偏长 * */ public void ReadExcel() { // 将被表示成1.3922433397E10的手机号转化为13922433397,不一定是最好的转换方法 DecimalFormat df = new DecimalFormat("#"); try { // 创建对Excel工作簿文件的引用 HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream( fileToBeRead)); // System.out // .println("===SheetsNum===" + // workbook.getNumberOfSheets());//获取sheet数 for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) { if (null != workbook.getSheetAt(numSheets)) { HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet // System.out.println("+++getFirstRowNum+++" + // aSheet.getFirstRowNum());// // System.out.println("+++getLastRowNum+++" + // aSheet.getLastRowNum()); List<EmpPOJO> list = new ArrayList(); for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet .getLastRowNum() - 1; rowNumOfSheet++) { if (null != aSheet.getRow(rowNumOfSheet)) { HSSFRow row = aSheet.getRow(rowNumOfSheet + 1); EmpPOJO emp = new EmpPOJO(); HSSFCell cell = row.getCell((short) 0); emp .setEmpno((Long) (Long.valueOf(cell .getRichStringCellValue() .toString()) - 520)); cell = row.getCell((short) 1); emp.setEname(cell.getRichStringCellValue() .toString()); cell = row.getCell((short) 2); emp .setJob(cell.getRichStringCellValue() .toString()); cell = row.getCell((short) 3); if (cell.getRichStringCellValue().toString() .equals("")) { emp.setMgr(0L); } else { emp.setMgr(Long.valueOf(cell .getRichStringCellValue().toString())); } cell = row.getCell((short) 4); System.out.println(cell.getCellType()); // SimpleDateFormat format = new // SimpleDateFormat("yyyy-mm-dd HH:mm:ss"); Date date = cell.getDateCellValue(); emp.setHiredate(date); System.out.println("Hiredate : " + emp.getHiredate()); cell = row.getCell((short) 5); emp.setSal(Float.parseFloat(cell