Java_导出Excel

导出的Excel标题、Sheet名称、数据内容都可以使用中文​

一、pom.xml引入jar包

1 2 3 4 5

< dependency >              < groupId >org.apache.poi</ groupId >              < artifactId >poi-ooxml</ artifactId >              < version >3.13</ version >          </ dependency >

二、Excel操作内部类

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98

import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*;   public class ExportInternalUtil {      private XSSFWorkbook wb = null ;        private XSSFSheet sheet = null ;        /**       * @param wb       * @param sheet       */      public ExportInternalUtil(XSSFWorkbook wb, XSSFSheet sheet) {          this .wb = wb;          this .sheet = sheet;      }        /**       * 合并单元格后给合并后的单元格加边框       *       * @param region       * @param cs       */      public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {            int toprowNum = region.getFirstRow();          for ( int i = toprowNum; i <= region.getLastRow(); i++) {              XSSFRow row = sheet.getRow(i);              for ( int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {                  XSSFCell cell = row.getCell(j); // XSSFCellUtil.getCell(row,                                                  // (short) j);                  cell.setCellStyle(cs);              }          }      }        /**       * 设置表头的单元格样式       *       * @return       */      public XSSFCellStyle getHeadStyle() {          // 创建单元格样式          XSSFCellStyle cellStyle = wb.createCellStyle();          // 设置单元格的背景颜色为淡蓝色          cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);          cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);          // 设置单元格居中对齐          cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);          // 设置单元格垂直居中对齐          cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          // 创建单元格内容显示不下时自动换行          cellStyle.setWrapText( true );          // 设置单元格字体样式          XSSFFont font = wb.createFont();          // 设置字体加粗          font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);          font.setFontName( "宋体" );          font.setFontHeight(( short ) 200 );          cellStyle.setFont(font);          // 设置单元格边框为细线条          cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);          return cellStyle;      }        /**       * 设置表体的单元格样式       *       * @return       */      public XSSFCellStyle getBodyStyle() {          // 创建单元格样式          XSSFCellStyle cellStyle = wb.createCellStyle();          // 设置单元格居中对齐          cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);          // 设置单元格垂直居中对齐          cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          // 创建单元格内容显示不下时自动换行          cellStyle.setWrapText( true );          // 设置单元格字体样式          XSSFFont font = wb.createFont();          // 设置字体加粗          font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);          font.setFontName( "宋体" );          font.setFontHeight(( short ) 200 );          cellStyle.setFont(font);          // 设置单元格边框为细线条          cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);          return cellStyle;      } }

三、Excel操作类

       共外部调用，可设置Sheet名称、标题、数据等

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

import java.io.IOException; import java.util.ArrayList;   import javax.servlet.ServletOutputStream;   import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;   import com.james.domain.User;   public class ExportUtil {        public static void ExportExcel(String[] titles, ArrayList<User> list, ServletOutputStream outputStream) {          // 创建一个workbook 对应一个excel应用文件          XSSFWorkbook workBook = new XSSFWorkbook();          // 在workbook中添加一个sheet,对应Excel文件中的sheet          //Sheet名称，可以自定义中文名称          XSSFSheet sheet = workBook.createSheet( "Sheet1" );          ExportInternalUtil exportUtil = new ExportInternalUtil(workBook, sheet);          XSSFCellStyle headStyle = exportUtil.getHeadStyle();          XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();          // 构建表头          XSSFRow headRow = sheet.createRow( 0 );          XSSFCell cell = null ;            // 输出标题          for ( int i = 0 ; i < titles.length; i++) {              cell = headRow.createCell(i);              cell.setCellStyle(headStyle);              cell.setCellValue(titles[i]);          }          // 构建表体数据          for ( int j = 0 ; j < list.size(); j++) {              XSSFRow bodyRow = sheet.createRow(j + 1 );              User user = list.get(j);                cell = bodyRow.createCell( 0 );              cell.setCellStyle(bodyStyle);              cell.setCellValue(user.getLastIp());                cell = bodyRow.createCell( 1 );              cell.setCellStyle(bodyStyle);              cell.setCellValue(user.getLastVisit());                cell = bodyRow.createCell( 2 );              cell.setCellStyle(bodyStyle);              cell.setCellValue(user.getPassword());                            cell = bodyRow.createCell( 3 );              cell.setCellStyle(bodyStyle);              cell.setCellValue(user.getUserName());                            cell = bodyRow.createCell( 4 );              cell.setCellStyle(bodyStyle);              cell.setCellValue(user.getUserId());          }            try {              workBook.write(outputStream);              outputStream.flush();              outputStream.close();          } catch (IOException e) {              e.printStackTrace();          } finally {              try {                  outputStream.close();              } catch (IOException e) {                  e.printStackTrace();              }          }      } }

四、SpringMVC Controller层调用

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

@RequestMapping (value = "/excelDownload" )      public String exportExcel(HttpServletResponse response) {          try {              //String fileName = new String(("导出excel标题").getBytes(), "UTF-8") + ".xlsx";              String fileName= new String(( "导出excel标题" ).getBytes( "gb2312" ), "iso8859-1" )+ ".xlsx" ;              response.setContentType( "application/vnd.ms-excel;charset=UTF-8" );              response.setHeader( "Content-Disposition" , "attachment;filename=" + fileName);              response.setCharacterEncoding( "utf-8" );                // response.setHeader("Content-disposition", "attachment; filename="              // + "exdddcel" + ".xlsx");// 组装附件名称和格式                String[] titles = { "最后IP" , "最后访问时间" , "密码" , "用户名" , "用户编号" };                /*SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");              String date = df.format(new Date());              Date dateNow = null;              try {                  dateNow = df.parse(date);              } catch (ParseException e) {                  e.printStackTrace();              }*/              Date dateNow = new Date();                            ArrayList<User> users = new ArrayList<User>();              User user = new User();              user.setLastIp( "127.0.0.1" );              user.setLastVisit(dateNow);              user.setPassword( "123" );              user.setUserId( 1 );              user.setUserName( "名称：James" );              users.add(user);                user = new User();              user.setLastIp( "192.0.0.1" );              user.setLastVisit(dateNow);              user.setPassword( "456" );              user.setUserId( 2 );              user.setUserName( "名称：Mary" );              users.add(user);                ServletOutputStream outputStream = response.getOutputStream();              ExportUtil.ExportExcel(titles, users, outputStream);          } catch (IOException e) {              e.printStackTrace();          }          return null ;      }

五、程序中用到的实体类User

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

import java.io.Serializable; import java.util.Date;   public class User implements Serializable {      private int userId;        private String userName;        private String password;        private String lastIp;        private Date lastVisit;        public int getUserId() {          return userId;      }        public void setUserId( int userId) {          this .userId = userId;      }        public String getUserName() {          return userName;      }        public void setUserName(String userName) {          this .userName = userName;      }        public String getPassword() {          return password;      }        public void setPassword(String password) {          this .password = password;      }        public String getLastIp() {          return lastIp;      }        public void setLastIp(String lastIp) {          this .lastIp = lastIp;      }        public Date getLastVisit() {          return lastVisit;      }        public void setLastVisit(Date lastVisit) {          this .lastVisit = lastVisit;      } }

六、异常情况

1、标题乱码（注释代码会出现标题乱码）

1 2	//String fileName = new String(("导出excel标题").getBytes(), "UTF-8") + ".xlsx";              String fileName= new String(( "导出excel标题" ).getBytes( "gb2312" ), "iso8859-1" )+ ".xlsx" ;

七、参考文档

1、http://webook-java.iteye.com/blog/1699621

2、http://www.cnblogs.com/yjmyzz/p/4206463.html

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/gossip/p/5218029.html