文章出處
本文采用 poi 利用java程序實現excel表格的上傳下載操作。
例：
實體類
package test;/*** Created by LiuD on 2016/11/18.*/public class CompStaffCriteria {private String staffName;private String certNum;private Integer anmeldungProvinceName;private Integer anmeldungCityName;private String mobile;private String anmeldungProperty;private String bankAcctNo;private String bankAcctName;private Long payBasic;private String firstFlag;private String email;public String getStaffName() {return staffName;}public void setStaffName(String staffName) {this.staffName = staffName;}public String getCertNum() {return certNum;}public void setCertNum(String certNum) {this.certNum = certNum;}public Integer getAnmeldungProvinceName() {return anmeldungProvinceName;}public void setAnmeldungProvinceName(Integer anmeldungProvinceName) {this.anmeldungProvinceName = anmeldungProvinceName;}public Integer getAnmeldungCityName() {return anmeldungCityName;}public void setAnmeldungCityName(Integer anmeldungCityName) {this.anmeldungCityName = anmeldungCityName;}public String getMobile() {return mobile;}public void setMobile(String mobile) {this.mobile = mobile;}public String getAnmeldungProperty() {return anmeldungProperty;}public void setAnmeldungProperty(String anmeldungProperty) {this.anmeldungProperty = anmeldungProperty;}public String getBankAcctNo() {return bankAcctNo;}public void setBankAcctNo(String bankAcctNo) {this.bankAcctNo = bankAcctNo;}public String getBankAcctName() {return bankAcctName;}public void setBankAcctName(String bankAcctName) {this.bankAcctName = bankAcctName;}public Long getPayBasic() {return payBasic;}public void setPayBasic(Long payBasic) {this.payBasic = payBasic;}public String getFirstFlag() {return firstFlag;}public void setFirstFlag(String firstFlag) {this.firstFlag = firstFlag;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}}下載 Util
package test;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.DataValidationConstraint;import org.apache.poi.ss.usermodel.DataValidationHelper;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import java.util.ArrayList;import java.util.List;/*** Created by LiuD on 2016/12/13.*/public class TestExcelDown {public static HSSFWorkbook getExcelTemplate(String fileName) {//列標題 1String[] handerFirst = {"必填項", "必填項", "選擇項(請查看下拉框)", "必填項", "必填項", "必填項", "數字類型", "選擇項(請查看下拉框)", "必填項", ""};//列標題 2String[] handerSecond = {"姓名", "身份證號", "戶籍性質", "戶口所在地","銀行卡號", "銀行賬戶名", "社保基數", "是否首次參保", "手機號碼", "郵箱地址"};//下拉框數據List downData = new ArrayList<>();String[] str2 = {"農業", "非農業"};String[] str4 = {"是", "否"};downData.add(str2);downData.add(str4);String[] downRows = {"2", "7"};if (null != fileName) {switch (fileName) {case "北京" : return getExcelDownlod(fileName, handerFirst, handerSecond, downData, downRows);case "上海" : return getExcelDownlod(fileName, handerFirst, handerSecond, downData, downRows);default : break;}}return null;}public static HSSFWorkbook getExcelDownlod(String fileName, String[] handerFirst, String[] handerSecond, List downData, String[] downRows) {// 新建一個workbookHSSFWorkbook workBook = new HSSFWorkbook();//新建sheetHSSFSheet sheet = workBook.createSheet();//表頭字體HSSFCellStyle style_head = workBook.createCellStyle();HSSFFont hssfFont = workBook.createFont();// 設置字體名稱hssfFont.setFontName("華文行楷");// 設置字號hssfFont.setFontHeightInPoints((short) 24);style_head.setFont(hssfFont);// 水平居中style_head.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 垂直居中style_head.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//設置表頭信息HSSFRow row = sheet.createRow(0);HSSFCell cell = row.createCell(0);cell.setCellValue("人員增報模板-"+fileName);cell.setCellStyle(style_head);CellRangeAddress region = new CellRangeAddress(0, 0, 0, 9);sheet.addMergedRegion(region);//標題 1 字體HSSFCellStyle styleFirst= workBook.createCellStyle();HSSFFont fontFirst = workBook.createFont();fontFirst.setFontName("微軟雅黑");fontFirst.setFontHeightInPoints((short) 10);fontFirst.setColor(HSSFColor.RED.index);styleFirst.setFont(fontFirst);//標題 1 信息HSSFRow rowFirst =sheet.createRow(1);for (int x = 0; x < handerFirst.length; x++) {HSSFCell cellFirst = rowFirst.createCell(x);sheet.setColumnWidth(x,3000);cellFirst.setCellStyle(styleFirst);cellFirst.setCellValue(handerFirst[x]);}//標題 2 字體HSSFCellStyle styleSecond = workBook.createCellStyle();HSSFFont fontSecond = workBook.createFont();fontSecond.setFontName("華文行楷");fontSecond.setFontHeightInPoints((short) 12);styleSecond.setAlignment(HSSFCellStyle.ALIGN_CENTER);fontSecond.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);styleSecond.setFont(fontSecond);//標題 2 信息HSSFRow rowSecond = sheet.createRow(2);for (int i = 0; i < handerSecond.length; i++) {HSSFCell hssfCell = rowSecond.createCell(i);sheet.setColumnWidth(i,5000);hssfCell.setCellStyle(styleSecond);hssfCell.setCellValue(handerSecond[i]);}//設置下拉框for (int r = 0; r < downData.size(); r++) {//取出下拉框的值String[] dData = downData.get(r);int rownum =Integer.parseInt(downRows[r]);//下拉框生效位置sheet.addValidationData(setDataValidation(sheet, dData, 3, 50000, rownum ,rownum));}return workBook;}private static DataValidation setDataValidation(HSSFSheet sheet, String[] dData, int firstRow, int endRow, int firstNum, int endNum) {DataValidationHelper helper = sheet.getDataValidationHelper();//加載下拉框內容DataValidationConstraint constraint= helper.createExplicitListConstraint(dData);CellRangeAddressList rangeAddressList = new CellRangeAddressList(firstRow, endRow, firstNum, endNum);DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);return dataValidation;}}
測試類package test;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileOutputStream;
/**
* Created by LiuD on 2016/12/16.
*/
public class TestDownDemo {//下載
public static void main(String[] args) {
String fileName = "北京";
try {
HSSFWorkbook workbook = ExcelUtil.getExcelTemplate(fileName);
FileOutputStream file = new FileOutputStream("F:\\10086.xls");
workbook.write(file);
file.flush();
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
} 下載Util
package test;import java.io.FileInputStream;import java.io.IOException;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/*** Created by LiuD on 2016/12/16.*/public class TestExcelReader {//讀入excle表格 并處理 返回需要的criteriapublic static List getCompStaff (String filePath) throws Exception{List criteriaList = new ArrayList<>();String[] s;Workbook workBook = getWorkBook(filePath);//得到工作表Sheet sheet = workBook.getSheetAt(0);CompStaffCriteria criteria;//獲得總行數int totalRowNum = sheet.getLastRowNum();for (int i = 3; i <= totalRowNum; i++) {//獲取整一行對象Row row = sheet.getRow(i);//獲取整一行有多少列int coloumNum=sheet.getRow(i).getPhysicalNumberOfCells();s = getExcelRow(row, coloumNum);criteria = validate(s, i + 1);criteriaList.add(criteria);}return criteriaList;}//判斷文件類型public static Workbook getWorkBook(String filePath) throws Exception{//判斷是否為excel的類型if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {throw new Exception("上傳格式錯誤");}FileInputStream fis = null;org.apache.poi.ss.usermodel.Workbook workBook = null;try {fis = new FileInputStream(filePath);//2003版本的excel，用.xls結尾workBook = new HSSFWorkbook(fis);} catch (Exception ex) {try {//2007版本的excel，用.xlsx結尾workBook = new XSSFWorkbook(fis);} catch (IOException e) {e.printStackTrace();}}return workBook;}//將取到的每行數據轉換成數組存儲public static String[] getExcelRow(Row row, int coloumNum) {String[] s;StringBuilder builder = new StringBuilder();for (int x = 0; x < coloumNum; x++) {//獲取每一行對應HSSFCell cell = (HSSFCell) row.getCell(x);builder.append(getStringCellValue(cell)+",");}s = builder.toString().split(",");return s;}//獲取單元格內有效數據private static String getStringCellValue(HSSFCell cell) {StringBuilder sb = new StringBuilder();//轉換數字格式取值跟 E10 問題DecimalFormat format = new DecimalFormat("#");switch (cell.getCellType()) {//數字case HSSFCell.CELL_TYPE_NUMERIC:sb.append(format.format(cell.getNumericCellValue()));break;//字符串case HSSFCell.CELL_TYPE_STRING:sb.append(cell.getStringCellValue());break;//布爾case HSSFCell.CELL_TYPE_BOOLEAN:sb.append(cell.getBooleanCellValue());break;//公式case HSSFCell.CELL_TYPE_FORMULA:sb.append(cell.getCellFormula());break;//空值case HSSFCell.CELL_TYPE_BLANK:sb.append("");break;//故障case HSSFCell.CELL_TYPE_ERROR:sb.append("");break;default:sb.append("");break;}if (sb.equals("") || sb == null) {return sb.append("").toString();}return sb.toString();}//驗證輸入參數正確性public static CompStaffCriteria validate(String[] s, int i) throws Exception{CompStaffCriteria criteria = new CompStaffCriteria();//用戶名if (StringUtils.isEmpty(s[0].trim())) {throw new Exception("第" + i + "行記錄解析失敗，用戶名不能為空");}//身份證if (StringUtils.isEmpty(s[1].trim())) {throw new Exception("第" + i + "行記錄解析失敗，身份證號未填寫");}//戶籍if (StringUtils.isEmpty(s[2].trim())) {throw new Exception("第" + i + "行記錄解析失敗，戶籍狀態未填寫");}if (!s[2].equals("非農業") && !s[2].equals("農業")) {throw new Exception("第" + i + "行記錄解析失敗，戶籍性質選擇錯誤");}//戶口所在地if (StringUtils.isEmpty(s[3].trim())) {throw new Exception("第" + i + "行記錄解析失敗，戶口所在地未填寫");}if (s[3].indexOf("市") == -1) {throw new Exception("第" + i + "行記錄解析失敗，戶口所在地填寫錯誤");}//銀行卡號if (StringUtils.isEmpty(s[4].trim())) {throw new Exception("第" + i + "行記錄解析失敗，銀行卡號未填寫");}//銀行賬戶名if (StringUtils.isEmpty(s[5].trim())) {throw new Exception("第" + i + "行記錄解析失敗，銀行賬戶名未填寫");}//社保基數if (StringUtils.isEmpty(s[6].trim())) {throw new Exception("第" + i + "行記錄解析失敗，社保基數未填寫");}//是否首次參保if (StringUtils.isEmpty(s[7].trim())) {throw new Exception("第" + i + "行記錄解析失敗，是否首次參保未填寫");}if (!s[7].trim().equals("是") && !s[7].trim().equals("否")) {throw new Exception("第" + i + "行記錄解析失敗，是否首次參保狀態選擇錯誤");}//手機號檢查String regMo = "^((13[0-9])|(15[^4,\\D])|(18[0,5-9])|(177))\\d{8}$";if (StringUtils.isEmpty(s[8].trim())) {throw new Exception("第" + i + "行記錄解析失敗，手機號未填寫");}if (!s[8].trim().matches(regMo)) {throw new Exception("第" + i + "行記錄解析失敗，手機號格式不正確");}//郵箱地址if (s.length>9) {String regEx = "^([a-zA-Z0-9]*[-_]?[a-zA-Z0-9]+)*@([a-zA-Z0-9]*[-_]?[a-zA-Z0-9]+)+[\\.][A-Za-z]{2,3}([\\.][A-Za-z]{2})?$";if (!s[9].trim().matches(regEx)) {throw new Exception("第" + i + "行記錄解析失敗，郵箱格式不正確");}criteria.setEmail(s[9]);}criteria.setStaffName(s[0]);criteria.setCertNum(s[1]);if (s[2].trim().equals("非農業")){criteria.setAnmeldungProperty("TOWN");}if (s[2].trim().equals("農業")){criteria.setAnmeldungProperty("COUNTRY");}int numProvince = s[3].indexOf("省");if (numProvince > -1) {String provinceName = s[3].substring(0, numProvince);criteria.setAnmeldungProvinceName(provinceName);int numCity = s[3].indexOf("市");String cityName = s[3].substring(numProvince +1, numCity);criteria.setAnmeldungCityName(cityName);} else {int numCity = s[3].indexOf("市");String cityName = s[3].substring(0, numCity);criteria.setAnmeldungProvinceName(cityName);criteria.setAnmeldungCityName(cityName);}criteria.setBankAcctNo(s[4]);criteria.setBankAcctName(s[5]);criteria.setPayBasic(Long.parseLong(s[6]));if (s[7].trim().equals("是")) {criteria.setFirstFlag("Y");}if (s[7].trim().equals("否")) {criteria.setFirstFlag("N");}criteria.setMobile(s[8]);return criteria;}//非空判斷public static boolean isEmpty(String src) {if (src == null || "".equals(src)) {return true;}return false;}}測試類
package test;import test.CompStaffCriteria;/*** Created by LiuD on 2016/11/18.*/public class TestReaderDemo {// 下載public static void main(String[] args) throws Exception {List u=TestExcelReader.getCompStaff("F:\\10086.xls");}}
以上就是java代碼通過poi實現excel文件的上傳下載；數據導入并下載原理基本和以上代碼一致；大概思路為1、拿到數據庫的所有數據并封裝成一個list集合2、將list集合傳入下載Util3、遍歷集合，并在表頭數量基礎上每次蹭加一行row 例： HSSFRow row = sheet.createRow(i+3); //表頭有3行內容4、拿到實體中的每個元素值，并添加到Cell中 例：cell = row.createCell(0); cell.setCellValue(user.getUserId()); 看文倉www.kanwencang.com網友整理上傳,為您提供最全的知識大全,期待您的分享，轉載請注明出處。
歡迎轉載：http://www.kanwencang.com/bangong/20170104/81424.html
文章列表
全站熱搜
留言列表
