我們知道要建立一張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<Student> studens=new ArrayList<Student>();
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 <studens .size(); i++) {
58 sheet.addMergedRegion(new Region(1+i+1, (short) 0, 1+i+1, (short)0));
59 HSSFRow rows= sheet.createRow(1+i+1); ////建立第二列 标題
60 HSSFCell fens = rows.createCell((short)0); //--->建立一個單元格
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;//獲得execl
23 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 exception
53 }
54 }
55
56 private static String getCellFormatValue(HSSFCell cell) {
57 String cellvalue = "";
58 if (cell != null) {
59 // 判斷目前Cell的Type
60 switch (cell.getCellType()) {
61 // 如果目前Cell的Type為NUMERIC
62 case HSSFCell.CELL_TYPE_NUMERIC:
63 case HSSFCell.CELL_TYPE_FORMULA: {
64 // 判斷目前的cell是否為Date
65 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為STRIN
78 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寶下載下傳路徑
https://download.csdn.net/download/cengjianggh/10418815---------------------------------------------------------------------------------------------------------------------更新分割線-------------------------------------------------------------------------------------------
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<BsActLog> 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 else
30 {
31 if (buf.length() == len)
32 {
33 //這裡的意義在于盡可能少的toString，因為會重新生成字元串
34 buf = null;
35 return source;
36 }
37 else
38 {
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 == 0x303D
65 || codePoint == 0x2049
66 || codePoint == 0x203C
67 || (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) // 箭頭A
75 || (codePoint >= 0x2900 && codePoint <= 0x297F) // 箭頭B
76 || (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 } 歡迎大家一起說出自己的想法。