POI实现Excel导入导出-白红宇

POI实现Excel导入导出

阅读量：5030 次

发布时间：2019-06-12

本文共 16207 字，大约阅读时间需要 54 分钟。

我们知道要创建一张excel你得知道excel由什么组成，比如说sheet也就是一个工作表格，例如一行，一个单元格，单元格格式，单元格内容格式…这些都对应着poi里面的一个类。

一个excel表格：

HSSFWorkbook wb = new HSSFWorkbook();

一个工作表格（sheet）：

HSSFSheet sheet = wb.createSheet("表格");

一行（row）：

HSSFRow row1 = sheet.createRow(0);

一个单元格（cell）：

HSSFCell cell2 = row2.createCell((short)0)

单元格格式（cellstyle）：

HSSFCellStyle style4 = wb.createCellStyle()

单元格内容格式（）

HSSFDataFormat format= wb.createDataFormat();

1：首先创建一个po对象

package entity;public class Student {    private int no;    private String name;    private int age;    private String grage;    public Student(int no, String name, int age, String grage) {        super();        this.no = no;        this.name = name;        this.age = age;        this.grage = grage;    }    public int getNo() {        return no;    }    public void setNo(int no) {        this.no = no;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public String getGrage() {        return grage;    }    public void setGrage(String grage) {        this.grage = grage;    }    }

2实现导出的功能：

1 package demo; 2  3 import java.io.FileOutputStream; 4 import java.io.IOException; 5 import java.sql.SQLException; 6 import java.util.ArrayList; 7 import java.util.Date; 8 import java.util.List; 9 10 import org.apache.poi.hssf.usermodel.HSSFCell;11 import org.apache.poi.hssf.usermodel.HSSFCellStyle;12 import org.apache.poi.hssf.usermodel.HSSFDataFormat;13 import org.apache.poi.hssf.usermodel.HSSFRow;14 import org.apache.poi.hssf.usermodel.HSSFSheet;15 import org.apache.poi.hssf.usermodel.HSSFWorkbook;16 import org.apache.poi.hssf.util.Region;17 import org.apache.poi.ss.usermodel.Font;18 19 import entity.Student;20 21 public class Export_demo {22     public static void main(String[] args) {23         export();24     }25     26     public static void export(){27         List
     
       studens=new ArrayList
      
       ();28         for (int i = 1; i <=20; i++) {29             Student s=new Student(i, "a"+i, 20+i-20, "三年级");30             studens.add(s);31         }32         33         HSSFWorkbook wb = new HSSFWorkbook();//创建一个excel文件34         HSSFSheet sheet=wb.createSheet("学生信息");//创建一个工作薄35         sheet.setColumnWidth((short)3, 20* 256);    //---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的  36         sheet.setColumnWidth((short)4, 20* 256);    //--->第一个参数是指哪个单元格，第二个参数是单元格的宽度  37         sheet.setDefaultRowHeight((short)300);    // ---->有得时候你想设置统一单元格的高度，就用这个方法38         HSSFDataFormat format= wb.createDataFormat();   //--->单元格内容格式  39         HSSFRow row1 = sheet.createRow(0);   //--->创建一行  40           // 四个参数分别是：起始行，起始列，结束行，结束列 (单个单元格) 41         sheet.addMergedRegion(new Region(0, (short) 0, 0, (short)5));//可以有合并的作用 42         HSSFCell cell1 = row1.createCell((short)0);   //--->创建一个单元格  43         cell1.setCellValue("学生信息总览");44         45        46         sheet.addMergedRegion(new Region(1, (short) 0, 1, (short)0));47         HSSFRow row2= sheet.createRow(1);   创建第二列 标题48         HSSFCell fen = row2.createCell((short)0);   //--->创建一个单元格  49         fen.setCellValue("编号/属性 ");50         HSSFCell no = row2.createCell((short)1);   //--->创建一个单元格  51         no.setCellValue("姓名 ");52         HSSFCell age = row2.createCell((short)2);   //--->创建一个单元格  53         age.setCellValue("年龄 ");54         HSSFCell grage = row2.createCell((short)3);   //--->创建一个单元格  55         grage.setCellValue("年级 ");56         57         for (int i = 0; i 
       
        创建一个单元格  61             fens.setCellValue(studens.get(i).getNo());62             HSSFCell nos = rows.createCell((short)1);   //--->创建一个单元格  63             nos.setCellValue(studens.get(i).getName());64             HSSFCell ages = rows.createCell((short)2);   //--->创建一个单元格  65             ages.setCellValue(studens.get(i).getAge());66             HSSFCell grages = rows.createCell((short)3);   //--->创建一个单元格  67             grages.setCellValue(studens.get(i).getGrage());68         }69         FileOutputStream fileOut = null;  70         try{              71             fileOut = new FileOutputStream("d:\\studens.xls");  72             wb.write(fileOut);  73             //fileOut.close();  74             System.out.print("OK");  75         }catch(Exception e){  76             e.printStackTrace();  77         }  78         finally{  79             if(fileOut != null){  80                 try {  81                     fileOut.close();  82                 } catch (IOException e) {  83                     // TODO Auto-generated catch block  84                     e.printStackTrace();  85                 }  86             }  87         }  88     }89 }

效果图：

3实现导入的功能：

1 package demo; 2  3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.InputStream; 6 import java.text.SimpleDateFormat; 7 import java.util.ArrayList; 8 import java.util.Date; 9 import java.util.List;10 11 import org.apache.poi.hssf.usermodel.HSSFCell;12 import org.apache.poi.hssf.usermodel.HSSFDateUtil;13 import org.apache.poi.hssf.usermodel.HSSFRow;14 import org.apache.poi.hssf.usermodel.HSSFSheet;15 import org.apache.poi.hssf.usermodel.HSSFWorkbook;16 import org.apache.poi.poifs.filesystem.POIFSFileSystem;17 18 import entity.Student;19 20 public class Import_demo {21     private static POIFSFileSystem fs;//poi文件流22     private static HSSFWorkbook wb;//获得execl23     private static HSSFRow row;//获得行24     private static HSSFSheet sheet;//获得工作簿25     26     public static void main(String[] args) throws FileNotFoundException {27         InputStream in= new FileInputStream("d:\\studens.xls");28         imports(in);29     }30     31     public static void imports(InputStream in ){32         String str = "";33         try {34              fs = new POIFSFileSystem(in);35              wb = new HSSFWorkbook(fs);36              sheet=wb.getSheetAt(0);37              //int rowfirst=sheet.getFirstRowNum();38              int rowend=sheet.getLastRowNum();39              for (int i = 2; i <=rowend; i++) {40                 row=sheet.getRow(i);41                 //System.out.println(row.get);42                 int colNum = row.getPhysicalNumberOfCells();//一行总列数43                  int j = 0;44                     while (j < colNum) {45                         str += getCellFormatValue(row.getCell((short) j)).trim() + "-";46                         j++;47                     }48                     System.out.println(str);49                     str="";50             }51         } catch (Exception e) {52             // TODO: handle exception53         }54     }55     56     private static String getCellFormatValue(HSSFCell cell) {57         String cellvalue = "";58         if (cell != null) {59             // 判断当前Cell的Type60             switch (cell.getCellType()) {61                 // 如果当前Cell的Type为NUMERIC62                 case HSSFCell.CELL_TYPE_NUMERIC:63                 case HSSFCell.CELL_TYPE_FORMULA: {64                     // 判断当前的cell是否为Date65                     if (HSSFDateUtil.isCellDateFormatted(cell)) {66                         Date date = cell.getDateCellValue();67                         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");68                         cellvalue = sdf.format(date);69                     }70                     // 如果是纯数字71                     else {72                         // 取得当前Cell的数值73                         cellvalue = String.valueOf(cell.getNumericCellValue());74                     }75                     break;76                 }77                 // 如果当前Cell的Type为STRIN78                 case HSSFCell.CELL_TYPE_STRING:79                     // 取得当前的Cell字符串80                     cellvalue = cell.getRichStringCellValue().getString();81                     break;82                 // 默认的Cell值83                 default:84                     cellvalue = " ";85             }86         } else {87             cellvalue = "";88         }89         return cellvalue;90     }91     92 }

效果：

代码和jar宝下载路径

---------------------------------------------------------------------------------------------------------------------更新分割线-------------------------------------------------------------------------------------------

2018年7月31日 14:54:00

实现导出poi数据到excel 在浏览器上弹出下载标签

　　接受数据查询的参数，然后查询数据库得到list集合的po对象转换为excel然后输出给浏览器

@RequiresPermissions("/actLog/postActLogSel")    @RequestMapping(value = "/actLog/excel")    public @ResponseBody  void getexcel(DataGridModel dgm,HttpServletResponse response,HttpServletRequest request) throws Exception {        response.setCharacterEncoding("UTF-8");        response.setContentType("application/x-download");        String fileName = "埋点登录.xlsx";        fileName = URLEncoder.encode(fileName, "UTF-8");        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);        XSSFWorkbook wb=actLog.getExcel(dgm);        try {            OutputStream out = response.getOutputStream();            wb.write(out);            out.close();            wb.close();        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();    }        }

1 if("post".equals(type)){ 2         List
     
       log=actLog.getActPostcounts(map); 3         wb = new XSSFWorkbook(); 4         XSSFSheet sheet = wb.createSheet("帖子埋点"); 5         sheet.setColumnWidth(0, 20 * 256);sheet.setColumnWidth(4, 20 * 256);sheet.setColumnWidth(8, 20 * 256); 6         sheet.setColumnWidth(1, 20 * 256);sheet.setColumnWidth(5, 20 * 256);sheet.setColumnWidth(9, 20 * 256); 7         sheet.setColumnWidth(2, 20 * 256);sheet.setColumnWidth(6, 20 * 256);sheet.setColumnWidth(10, 20 * 256); 8         sheet.setColumnWidth(3, 20 * 256);sheet.setColumnWidth(7, 20 * 256); 9         XSSFRow row = sheet.createRow(0);10         XSSFCell cell = row.createCell(0);11         cell=getStyle(wb, cell);//设置样式，可以不要12         cell.setCellValue("日期 ");13         XSSFCell cell2 = row.createCell(1);14         cell2.setCellValue("标题 ");15         cell2=getStyle(wb, cell2);16         XSSFCell cell3 = row.createCell(2);17         cell3.setCellValue("用户名 ");18         cell3=getStyle(wb, cell3);19         XSSFCell cell5 = row.createCell(3);20         cell5.setCellValue("PV ");21         cell5=getStyle(wb, cell5);22         XSSFCell cell6 = row.createCell(4);23         cell6.setCellValue("UV ");24         cell6=getStyle(wb, cell6);25         XSSFCell cell7 = row.createCell(5);26         cell7.setCellValue("回复人数 ");27         cell7=getStyle(wb, cell7);28         XSSFCell cell8 = row.createCell(6);29         cell8.setCellValue("回复次数 ");30         cell8=getStyle(wb, cell8);31         XSSFCell cell9 = row.createCell(7);32         cell9.setCellValue("点赞数 ");33         cell9=getStyle(wb, cell9);34         XSSFCell cell10 = row.createCell(8);35         cell10.setCellValue("收藏数 ");36         cell10=getStyle(wb, cell10);37         XSSFCell cell11 = row.createCell(9);38         cell11.setCellValue("是否首页置顶 ");39         cell11=getStyle(wb, cell11);40         XSSFCell cell12 = row.createCell(10);41         cell12.setCellValue("是否置顶 ");42         cell12=getStyle(wb, cell12);43         XSSFCell cell13 = row.createCell(11);44         cell13.setCellValue("是否精华 ");45         cell13=getStyle(wb, cell13);46         int i=1;47         for (BsActLog lo : log) {48             XSSFRow rows = sheet.createRow(i);49             XSSFCell cells = rows.createCell(0);50             cells.setCellValue(lo.getDay());51             cells=getStyle(wb, cells);52             XSSFCell cells2 = rows.createCell(1);53             cells2.setCellValue(lo.getTitle());54             cells2=getStyle(wb, cells2);55             XSSFCell cells3 = rows.createCell(2);56             String name=filterEmoji(lo.getUsername());//对emoji表情过滤，可以不要57             cells3.setCellValue(name);58             cells3=getStyle(wb, cells3);59             XSSFCell cells4 = rows.createCell(3);60             cells4.setCellValue(lo.getPv());61             cells4=getStyle(wb, cells4);62             XSSFCell cells5 = rows.createCell(4);63             cells5.setCellValue(lo.getUv());64             cells5=getStyle(wb, cells5);65             XSSFCell cells6 = rows.createCell(5);66             cells6.setCellValue(lo.getReplyperson());67             cells6=getStyle(wb, cells6);68             XSSFCell cells7 = rows.createCell(6);69             cells7.setCellValue(lo.getReplycount());70             cells7=getStyle(wb, cells7);71             XSSFCell cells8 = rows.createCell(7);72             cells8.setCellValue(lo.getLikecount());73             cells8=getStyle(wb, cells8);74             XSSFCell cells9 = rows.createCell(8);75             cells9.setCellValue(lo.getCollectcount());76             cells9=getStyle(wb, cells9);77             XSSFCell cells10 = rows.createCell(9);78             cells10.setCellValue(lo.getIsmainpagetop());79             cells10=getStyle(wb, cells10);80             XSSFCell cells11 = rows.createCell(10);81             cells11.setCellValue(lo.getIstop());82             cells11=getStyle(wb, cells11);83             XSSFCell cells12 = rows.createCell(11);84             cells12.setCellValue(lo.getIsdigestpost());85             cells12=getStyle(wb, cells12);86             i+=1;87         }88     }

如果浏览器弹不出下载框，则把前台的请求改为get请求

window.open('地址?'参数);

过滤emoji表情的方法

1 public static String filterEmoji(String source)//过滤emoji表情 2 { 3    if(source==null ||"".equals(source)) 4    { 5        return ""; 6    } 7     if (!containsEmoji(source)) 8     { 9         return source; //如果不包含，直接返回10     }11     //到这里铁定包含12     StringBuilder buf = null;13     int len = source.length();14     for (int i = 0; i < len; i++)15     {16         char codePoint = source.charAt(i);17         if (!isEmojiCharacter(codePoint))18         {19             if (buf == null)20             {21                 buf = new StringBuilder();22             }23             buf.append(codePoint);24         } else { } }25     if (buf == null)26     {27         return source; //如果没有找到 emoji表情，则返回源字符串28     }29     else30     {31         if (buf.length() == len)32         {33             //这里的意义在于尽可能少的toString，因为会重新生成字符串34             buf = null;35             return source;36         }37         else38         {39             return buf.toString();40         }41     }42 }43 public static boolean containsEmoji(String source)44 {45     if (source==null ||"".equals(source))46     {47         return false;48     }49     int len = source.length();50     for (int i = 0; i < len; i++)51     {52         char codePoint = source.charAt(i);53         if (isEmojiCharacter(codePoint))54         {55             //do nothing，判断到了这里表明，确认有表情字符56             return true;57         }58     }59     return false;60 }61 public static boolean isEmojiCharacter(char codePoint)62 {63     return (codePoint >= 0x2600 && codePoint <= 0x27BF) // 杂项符号与符号字体64            || codePoint == 0x303D65            || codePoint == 0x204966            || codePoint == 0x203C67            || (codePoint >= 0x2000 && codePoint <= 0x200F) //68            || (codePoint >= 0x2028 && codePoint <= 0x202F) //69            || codePoint == 0x205F //70            || (codePoint >= 0x2065 && codePoint <= 0x206F) //71                                                            /* 标点符号占用区域 */72            || (codePoint >= 0x2100 && codePoint <= 0x214F) // 字母符号73            || (codePoint >= 0x2300 && codePoint <= 0x23FF) // 各种技术符号74            || (codePoint >= 0x2B00 && codePoint <= 0x2BFF) // 箭头A75            || (codePoint >= 0x2900 && codePoint <= 0x297F) // 箭头B76            || (codePoint >= 0x3200 && codePoint <= 0x32FF) // 中文符号77            || (codePoint >= 0xD800 && codePoint <= 0xDFFF) // 高低位替代符保留区域78            || (codePoint >= 0xE000 && codePoint <= 0xF8FF) // 私有保留区域79            || (codePoint >= 0xFE00 && codePoint <= 0xFE0F) // 变异选择器80                                                            //   || (codePoint >= U + 2600 && codePoint <= 0xFE0F)81            || codePoint >= 0x10000; // Plane在第二平面以上的，char都不可以存，全部都转82 }

转载于:https://www.cnblogs.com/GH0522/p/9047472.html

你可能感兴趣的文章