真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

詳解java封裝實現(xiàn)Excel建表讀寫操作

對 Excel 進行讀寫操作是生產(chǎn)環(huán)境下常見的業(yè)務(wù)，網(wǎng)上搜索的實現(xiàn)方式都是基于POI和JXL第三方框架，但都不是很全面。小編由于這兩天剛好需要用到，于是就參考手寫了一個封裝操作工具，基本涵蓋了Excel表（分有表頭和無表頭）的創(chuàng)建，并對它們進行讀寫操作。為方便大家，有需要者可以點擊文后點解下載直接使用哦，當(dāng)然也可以根據(jù)自己需求舉一反三自己定制，相信對于聰明的你也不是什么難事。話不多說，直接貼源碼

創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比全椒網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式全椒網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋全椒地區(qū)。費用合理售后完善，十年實體公司更值得信賴。

pom.xml 文件：


 UTF-8
 1.8
 1.8
 

 
 
  junit
  junit
  4.11
  test
 
 
  org.apache.poi
  poi
  3.17
 
 
  org.projectlombok
  lombok
  1.18.0
  provided
 
 
  org.slf4j
  slf4j-log4j12
  1.8.0-beta2
  test
 
 
  log4j
  log4j
  1.2.17
 
 
  org.slf4j
  slf4j-api
  1.8.0-beta2

建表工具類：ExcelBuider.java

/**
   * 建表工具類
   * @author Sherman
   * email:1253950375@qq.com
   * created in 2018/8/24
   */
  @Slf4j
  public class ExcelBuilder {

   private static HSSFSheet sheet;
   private static HSSFWorkbook wb;
   private static boolean hasHeader;

   /**
    * 初始化
    * @param excellName 表名
    */
   public ExcelBuilder(String excellName) {
    wb = new HSSFWorkbook();
    sheet = wb.createSheet(excellName);
   }

   /**
    * 設(shè)置表頭，裝配表頭數(shù)據(jù)
    * @param value 字符串?dāng)?shù)組，用來作為表頭的值
    *
    */
   public ExcelBuilder header(String... value) {
    if (value != null && value.length != 0) {
     //設(shè)置表頭樣式
     HSSFCellStyle cellStyle = wb.createCellStyle();
     cellStyle.setFont(font("黑體", true, 12));
     HSSFRow row = sheet.createRow(0);
     for (int i = 0; i < value.length; i++) {
      HSSFCell cell = row.createCell(i);
      cell.setCellValue(value[i]);
      cell.setCellStyle(cellStyle);
     }
     hasHeader = true;
    }
    return this;
 }

 /**
  * excel 表內(nèi)容裝配
  * @param content 待裝配表格內(nèi)容的二維數(shù)組
  * @return
  */
 public ExcelBuilder content(List> content) {
  if (content != null && !content.isEmpty()) {
   int index;
   for (int i = 0; i < content.size(); i++) {
    index = hasHeader == false ? i : i + 1;
    HSSFRow row = sheet.createRow(index);
    for (int j = 0; j < content.get(i).size(); j++) {
     String r = "";
     Object value = content.get(i).get(j);
     //根據(jù)數(shù)據(jù)類型裝配
     if (value instanceof String) {
      r = (String) value;
     } else if (value instanceof Number) {
      r = String.valueOf(value);
     } else if (value instanceof BigDecimal) {
      r = String.valueOf(value);
     } else {
      if (!(value instanceof Date) && !(value instanceof Timestamp)) {
       if (!(value instanceof ZonedDateTime) && !(value instanceof LocalDateTime)) {
        if (value instanceof Enum) {
         r = ((Enum) value).name();
        } else if (value != null) {

         log.info("Error of create row, Unknow field type: " + value.getClass().getName());
        }
       } else {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        r = formatter.format((TemporalAccessor) value);
       }
      } else {
       DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
       r = sdf.format(value);
      }
     }

     row.createCell(j).setCellValue(r);
    }
   }
  }
  return this;
 }

 /**
  * 自動調(diào)整列寬大小
  */
 public ExcelBuilder autoColumnWidth() {
  for (int j = 0; j < sheet.getRow(0).getLastCellNum(); j++) {
   int maxLength = 0;
   for (int i = 0; i <= sheet.getLastRowNum(); i++) {
    String value = sheet.getRow(i).getCell(j).getStringCellValue();
    int length = 0;
    if (value != null) {
     length = value.getBytes().length;
    }
    if (length > maxLength) {
     maxLength = length;
    }
   }
   sheet.setColumnWidth(j, maxLength > 30 ? (30 * 256 + 186) : (maxLength * 256 + 186));
  }
  return this;
 }

 /**
  * 實例化
  * @param hasHeader 是否有表頭
  * @return Excel表格
  */
 public AbstractExcel build(Boolean hasHeader) {
  return hasHeader ? new HeaderExcel(sheet) : new NoHeaderExcel(sheet);
 }

 /**
  *
  * @param fontName 字體名字
  * @param isBold 是否粗體
  * @param fontSize 字體大小
  * @return 字體
  */
 private HSSFFont font(String fontName, boolean isBold, int fontSize) {
  HSSFFont font = wb.createFont();
  if (fontName != null) font.setFontName(fontName);
  else font.setFontName("黑體");
  font.setBold(isBold);
  font.setFontHeightInPoints((short) fontSize);
  return font;
 }

}

excel的抽象父類：

/**
 * @author Sherman
 * created in 2018/8/24
 */

public abstract class AbstractExcel {
 private final HSSFSheet sheet;

 public AbstractExcel() {
  HSSFWorkbook wb = new HSSFWorkbook();
  sheet = wb.createSheet();
 }

 public AbstractExcel(String sheetName){
  HSSFWorkbook wb = new HSSFWorkbook();
  sheet = wb.createSheet(sheetName);
 }

 public AbstractExcel(HSSFSheet sheet) {
  this.sheet = sheet;
 }



 public abstract List> getPayload();


 public void write(OutputStream op) throws IOException {
  sheet.getWorkbook().write(op);
  sheet.getWorkbook().close();
 }

 public String getStringFormatCellValue(HSSFCell cell) {
  String cellVal = "";
  DecimalFormat df = new DecimalFormat("#");
  switch (cell.getCellTypeEnum()) {
   case STRING:
    cellVal = cell.getStringCellValue();
    break;
   case NUMERIC:
    String dataFormat = cell.getCellStyle().getDataFormatString();
    if (DateUtil.isCellDateFormatted(cell)) {
     cellVal = df.format(cell.getDateCellValue());
    } else if ("@".equals(dataFormat)) {
     cellVal = df.format(cell.getNumericCellValue());
    } else {
     cellVal = String.valueOf(cell.getNumericCellValue());
     df = new DecimalFormat("#.#########");
     cellVal = df.format(Double.valueOf(cellVal));
    }
    break;
   case BOOLEAN:
    cellVal = String.valueOf(cell.getBooleanCellValue());
    break;
   case FORMULA:
    cellVal = String.valueOf(cell.getCellFormula());
    break;
   default:
    cellVal = "";
  }
  return cellVal;
 }


}

有表頭實現(xiàn)類

/**
 * @author Sherman
 * created in 2018/8/24
 */

public class HeaderExcel extends AbstractExcel {
 private final static boolean hasHeader = true;
 private final HSSFSheet sheet;

 public HeaderExcel(HSSFSheet sheet) {
  super(sheet);
  this.sheet = sheet;
 }

 public HeaderExcel(String sheetName, String excelPath) {
  HSSFWorkbook wb = null;
  try {
   wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));
  } catch (IOException e) {
   e.printStackTrace();
  }
  sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName);
 }

 @Override
 public List> getPayload() {
  List> payLoad = new ArrayList<>();
  HSSFRow headRow = sheet.getRow(0);
  for (int i = 1; i <= sheet.getLastRowNum(); i++) {
   HSSFRow currentRow = sheet.getRow(i);
   Map map = new HashMap<>();
   for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {
    map.put(getStringFormatCellValue(headRow.getCell(j)), getStringFormatCellValue(currentRow.getCell(j)));
   }
   payLoad.add(map);
  }
  return payLoad;
 }


}

無表頭實現(xiàn)類

/**
 * @author Sherman
 * created in 2018/8/24
 */

public class NoHeaderExcel extends AbstractExcel {
 private final static boolean hasHeader = false;
 private HSSFSheet sheet;

 public NoHeaderExcel(HSSFSheet sheet) {
  super(sheet);
  this.sheet = sheet;
 }

 public NoHeaderExcel(String sheetName, String excelPath) {
  HSSFWorkbook wb = null;
  try {
   wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));
  } catch (IOException e) {
   e.printStackTrace();
  }
  sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName);
 }


 @Override
 public List> getPayload() {
  List> payLoad = new ArrayList<>();
  for (int i = 0; i < sheet.getLastRowNum(); i++) {
   HSSFRow currentRow = sheet.getRow(i);
   Map map = new HashMap<>();
   for (int j = 0; j <= sheet.getRow(i).getLastCellNum(); j++) {
    map.put(String.valueOf(j), getStringFormatCellValue(currentRow.getCell(j)));
   }
   payLoad.add(map);
  }
  return payLoad;
 }


}

測試工具類：

/**
 * Unit test for simple App.
 */
public class AppTest 
{
 /**
  * 測試建表，寫表操作
  */
 @Test
 public void testExportExcel()
 {
  //測試數(shù)據(jù)
  String[] headers = new String[]{"A","B","C","D","E"};
   List> valueList = new LinkedList<>();
  for (char i = 'A'; i <= 'E' ; i++) {
   List rowList = new LinkedList<>();
   for (int j = 0; j <= 4; j++) {
    rowList.add(i+String.valueOf(j));
   }
   valueList.add(rowList);
  }

 AbstractExcel excel = new ExcelBuilder("報名表")
   .header(headers)
   .content(valueList)
   .autoColumnWidth()
   .build(true);

  try {
   File file = new File("E:\\excel\\test.xls");
   FileOutputStream op = new FileOutputStream(file);
   excel.write(op);
  } catch (IOException e) {
   e.printStackTrace();
  }
 }

 /**
  * 測試讀取表數(shù)據(jù)操作
  */
 @Test
 public void testImportExcel(){
  AbstractExcel excel = new HeaderExcel(null,"E:/excel/test.xls");
  List> values = excel.getPayload();
  values.forEach(stringStringMap -> {
   stringStringMap.entrySet().forEach(stringStringEntry -> {
    System.out.println(stringStringEntry.getKey()+"---->"+stringStringEntry.getValue());
   });

  });
 }

}
附圖：
測試1
詳解java封裝實現(xiàn)Excel建表讀寫操作
測試二：
詳解java封裝實現(xiàn)Excel建表讀寫操作
看起來效果還不錯，當(dāng)然還有很多不完善的地方，有需要的朋友可以在此基礎(chǔ)上擴展定制，例如讀取表數(shù)據(jù)結(jié)構(gòu)方式，實現(xiàn)行數(shù)增刪改查據(jù)或者創(chuàng)建表標(biāo)題等等。
或者有朋友有更好的實現(xiàn)方案，歡迎前來交流！
最后的最后，當(dāng)然忘不了附上笨工具的源碼啦！
https://github.com/yumiaoxia/excel-commom-demo.git
            
            
                        

            文章名稱：詳解java封裝實現(xiàn)Excel建表讀寫操作            

            當(dāng)前網(wǎng)址：http://weahome.cn/article/gohcpe.html
        
    






    

        

            
                
                
在線咨詢

            
        

    

    

        
微信咨詢

        

    

    

        
電話咨詢

        

            
028-86922220（工作日）

            
18980820575（7×24）

        

    

    

        

            
                
提交需求

            
        

    

    

        

            
                
返回頂部