工作中经常会用到excel的导入和导出功能,这里我提供导入和导出类。
导入类(需要注意的地方我注释里面写好了):
- package cn.teacheredu.utils;
- import java.io.InputStream;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.Arrays;
- import java.util.Date;
- import java.util.List;
- import java.util.regex.Matcher;
- import java.util.regex.Pattern;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.ss.usermodel.Cell;
- import org.apache.poi.ss.usermodel.DateUtil;
- 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;
- /**
- * 被解析的Excel最好是什么样的呢?
- * 单元格最好都是文本格式,保存数据前自己去转换,不用poi带的转换。
- * 第一列 和最后一列 必须是必填字段!!!这样的你用我这个Util,得到的List就很准确了,不会出现多余的行或列。
- * @author TMACJ
- * @version 0.000000.002899
- */
- public class ImportExcelUtil {
- private final static String excel2003L =".xls"; //2003- 版本的excel
- private final static String excel2007U =".xlsx"; //2007+ 版本的excel
- static SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- static short[] yyyyMMdd = {14, 31, 57, 58, 179, 184, 185, 186, 187, 188};
- static short[] HHmmss = {20, 32, 190, 191, 192};
- static List<short[]> yyyyMMddList = Arrays.asList(yyyyMMdd);
- static List<short[]> hhMMssList = Arrays.asList(HHmmss);
- /**
- * 描述:获取IO流中的数据,组装成List<List<Object>>对象
- * @param in,fileName
- * @return
- * @throws IOException
- */
- public List<List<String>> getBankListByExcel(InputStream in,String fileName) throws Exception{
- List<List<String>> list = null;
- //创建Excel工作薄
- Workbook work = this.getWorkbook(in,fileName);
- if(null == work){
- throw new Exception("创建Excel工作薄为空!");
- }
- Sheet sheet = null;
- Row row = null;
- Cell cell = null;
- list = new ArrayList<List<String>>();
- //遍历Excel中所有的sheet
- for (int i = 0; i < work.getNumberOfSheets(); i++) {
- sheet = work.getSheetAt(i);
- if(sheet==null){continue;}
- int totalCell = sheet.getRow(0).getPhysicalNumberOfCells();//标题行一共有多少列
- //遍历当前sheet中的所有行
- for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
- row = sheet.getRow(j);
- if(row==null || validateRow(row) || row.getPhysicalNumberOfCells() < totalCell){continue;} //3个条件,有一个为true就不会往list里加,不仅过滤空行还过滤了列数不够的行,这点要注意,要求表中前后的列都是必填的。
- //遍历所有的列
- List<String> li = new ArrayList<String>();
- for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
- cell = row.getCell(y);
- li.add(this.getCellData(cell));
- }
- list.add(li);
- }
- // 简单起见,这里只解析第一个工作簿!
- break;
- }
- work.close();
- return list;
- }
- // 过滤空行,(其中一行的数据的确都为空,可是其原本的格式还在,并没有连带删除,这样计算出来的行数就不真实,比真实的大)
- private boolean validateRow(Row row) throws Exception{
- // for (Cell cell : row) {
- //
- // }
- //只判断第一列。第一列为空就代表这行的数据无效
- if (row.getCell(0).getCellType() == Cell.CELL_TYPE_BLANK || "".equals(this.getCellData(row.getCell(0)))) {
- return true;
- }
- return false;//不是空行
- }
- /**
- * 描述:根据文件后缀,自适应上传文件的版本
- * @param inStr,fileName
- * @return
- * @throws Exception
- */
- public Workbook getWorkbook(InputStream inStr,String fileType) throws Exception{
- Workbook wb = null;
- if(excel2003L.equals(fileType)){
- wb = new HSSFWorkbook(inStr); //2003-
- }else if(excel2007U.equals(fileType)){
- wb = new XSSFWorkbook(inStr); //2007+
- }else{
- throw new Exception("解析的文件格式有误!");
- }
- return wb;
- }
- /**
- * 获取单元中值(字符串类型)
- *
- * @param cell
- * @return
- * @throws Exception
- */
- public String getCellData(Cell cell) throws Exception {
- String cellValue = "";
- if (cell != null) {
- try {
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_BLANK://空白
- cellValue = "";
- break;
- case Cell.CELL_TYPE_NUMERIC: //数值型 0----日期类型也是数值型的一种
- if (DateUtil.isCellDateFormatted(cell)) {
- short format = cell.getCellStyle().getDataFormat();
- if (yyyyMMddList.contains(format)) {
- sFormat = new SimpleDateFormat("yyyy-MM-dd");
- } else if (hhMMssList.contains(format)) {
- sFormat = new SimpleDateFormat("HH:mm:ss");
- }
- Date date = cell.getDateCellValue();
- cellValue = sFormat.format(date);
- } else {
- cell.setCellType(Cell.CELL_TYPE_STRING);
- cellValue = replaceBlank(cell.getStringCellValue());
- //Double numberDate = new BigDecimal(cell.getNumericCellValue()).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//似乎还是有点问题
- //cellValue = numberDate + "";
- }
- break;
- case Cell.CELL_TYPE_STRING: //字符串型 1
- cellValue = replaceBlank(cell.getStringCellValue());
- break;
- case Cell.CELL_TYPE_FORMULA: //公式型 2
- cell.setCellType(Cell.CELL_TYPE_STRING);
- cellValue = replaceBlank(cell.getStringCellValue());
- break;
- case Cell.CELL_TYPE_BOOLEAN: //布尔型 4
- cellValue = String.valueOf(cell.getBooleanCellValue());
- break;
- case Cell.CELL_TYPE_ERROR: //错误 5
- cellValue = "!#REF!";
- break;
- }
- } catch (Exception e) {
- throw new Exception("读取Excel单元格数据出错:" + e.getMessage());
- }
- }
- return cellValue;
- }
- public static String replaceBlank(String source) {
- String dest = "";
- if (source != null) {
- Pattern p = Pattern.compile("\t|\r|\n");
- Matcher m = p.matcher(source);
- dest = m.replaceAll("");
- }
- return dest.trim();
- }
- }
导出类(.XLS格式):
- package cn.teacheredu.utils;
- import java.io.OutputStream;
- import java.net.URLEncoder;
- import java.util.ArrayList;
- import java.util.Date;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.commons.lang.StringUtils;
- import org.apache.commons.lang3.time.DateFormatUtils;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRichTextString;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.ss.util.CellRangeAddress;
- /**
- * 通用的导出Excel类,如果需要自定义格式的,参照此类自己再写类或方法来实现
- * dataList里的每一个Object数组一个元素(object[0])都是序号,不可放真实数据
- * @author<span style="white-space:pre;"> </span>TMACJ
- */
- public class ExportExcelUtil {
- private String title; // 导出表格的表名
- private String[] rowName;// 导出表格的列名
- private List<Object[]> dataList = new ArrayList<Object[]>(); // 对象数组的List集合
- private HttpServletResponse response;
- private HttpServletRequest request;
- /**
- * 实例化导出类
- * @param title 导出表格的表名,最好是英文,中文可能出现乱码
- * @param rowName 导出表格的列名数组
- * @param dataList 对象数组的List集合
- * @param response
- */
- public ExportExcelUtil(String title,String[] rowName,List<Object[]> dataList, HttpServletRequest request, HttpServletResponse response){
- this.title=title;
- this.rowName=rowName;
- this.dataList=dataList;
- this.response = response;
- this.request = request;
- }
- // 导出数据
- public void exportData() throws Exception{
- HSSFWorkbook workbook =new HSSFWorkbook(); // 创建一个excel对象
- HSSFSheet sheet =workbook.createSheet(title); // 创建表格
- //sheet.setDefaultRowHeightInPoints(18.5f);
- // sheet样式定义
- HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook,16); // 头样式
- HSSFCellStyle columnStyle = this.getColumnStyle(workbook,14); // 标题样式
- HSSFCellStyle style = this.getStyle(workbook,11); // 单元格样式
- sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length-1)));// 合并第一行的所有列
- // 产生表格标题行
- HSSFRow rowm =sheet.createRow(0); // 行
- rowm.setHeightInPoints(26f);
- HSSFCell cellTiltle =rowm.createCell(0); // 单元格
- cellTiltle.setCellStyle(columnTopStyle);
- cellTiltle.setCellValue(title);
- int columnNum = rowName.length; // 表格列的长度
- HSSFRow rowRowName = sheet.createRow(1); // 在第二行创建行
- HSSFCellStyle cells =workbook.createCellStyle();
- cells.setBottomBorderColor(HSSFColor.BLACK.index);
- rowRowName.setRowStyle(cells);
- // 循环 将列名放进去
- for (int i = 0; i < columnNum; i++) {
- HSSFCell cellRowName = rowRowName.createCell(i);
- cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 单元格类型
- HSSFRichTextString text = new HSSFRichTextString(rowName[i]); // 得到列的值
- cellRowName.setCellValue(text); // 设置列的值
- cellRowName.setCellStyle(columnStyle); // 样式
- }
- // 将查询到的数据设置到对应的单元格中
- for (int i = 0; i < dataList.size(); i++) {
- Object[] obj = dataList.get(i);//遍历每个对象
- HSSFRow row = sheet.createRow(i+2);//创建所需的行数
- for (int j = 0; j < obj.length; j++) {
- HSSFCell cell = null; //设置单元格的数据类型
- if(j==0){
- // 第一列设置为序号
- cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
- cell.setCellValue(i+1);
- }else{
- cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
- if(!"".equals(obj[j]) && obj[j] != null){
- cell.setCellValue(obj[j].toString()); //设置单元格的值
- }else{
- cell.setCellValue(" ");
- }
- }
- cell.setCellStyle(style); // 样式
- }
- }
- // 让列宽随着导出的列长自动适应,但是对中文支持不是很好 也可能在linux(无图形环境的操作系统)下报错,报错再说
- for (int i = 0; i < columnNum; i++) {
- sheet.autoSizeColumn(i);
- sheet.setColumnWidth(i, sheet.getColumnWidth(i)+888);//适当再宽点
- }
- if(workbook !=null){
- // 输出到服务器上
- // FileOutputStream fileOutputStream = new FileOutputStream("D:/user.xls");
- // workbook.write(fileOutputStream);//将数据写出去
- // fileOutputStream.close();//关闭输出流
- // 输出到用户浏览器上
- OutputStream out = response.getOutputStream();
- try {
- // excel 表文件名
- String fileName = title + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xls";
- String fileName11 = "";
- String userAgent = request.getHeader("USER-AGENT");
- if(StringUtils.contains(userAgent, "Firefox") || StringUtils.contains(userAgent, "firefox")){//火狐浏览器
- fileName11 = new String(fileName.getBytes(), "ISO8859-1");
- }else{
- fileName11 = URLEncoder.encode(fileName,"UTF-8");//其他浏览器
- }
- String headStr = "attachment; filename=\"" + fileName11 + "\"";
- response.setContentType("APPLICATION/OCTET-STREAM");
- response.setCharacterEncoding("UTF-8");
- response.setHeader("Content-Disposition", headStr);
- workbook.write(out);
- out.flush();
- workbook.close();
- } catch (Exception e) {
- throw e;
- } finally {
- if (null != out) {
- out.close();
- }
- }
- }
- }
- public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook,int fontSize) {
- // 设置字体
- HSSFFont font = workbook.createFont();
- //设置字体大小
- font.setFontHeightInPoints((short)fontSize);
- //字体加粗
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- //设置字体名字
- font.setFontName("宋体");
- //设置样式;
- HSSFCellStyle style = workbook.createCellStyle();
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- return style;
- }
- public HSSFCellStyle getColumnStyle(HSSFWorkbook workbook,int fontSize) {
- // 设置字体
- HSSFFont font = workbook.createFont();
- //设置字体大小
- font.setFontHeightInPoints((short)fontSize);
- //字体加粗
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- //设置字体名字
- font.setFontName("宋体");
- //设置样式;
- HSSFCellStyle style = workbook.createCellStyle();
- //设置底边框;
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- //设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- //设置左边框;
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- //设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- //设置右边框;
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- //设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- //设置顶边框;
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- //设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- return style;
- }
- public HSSFCellStyle getStyle(HSSFWorkbook workbook,int fontSize) {
- //设置字体
- HSSFFont font = workbook.createFont();
- //设置字体大小
- font.setFontHeightInPoints((short)fontSize);
- //字体加粗
- //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- //设置字体名字
- font.setFontName("宋体");
- //设置样式;
- HSSFCellStyle style = workbook.createCellStyle();
- //设置底边框;
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- //设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- //设置左边框;
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- //设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- //设置右边框;
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- //设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- //设置顶边框;
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- //设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- return style;
- }
- }
导出类(.xlsx格式):
- package cn.teacheredu.utils;
- import java.io.OutputStream;
- import java.net.URLEncoder;
- import java.util.ArrayList;
- import java.util.Date;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.commons.lang.StringUtils;
- import org.apache.commons.lang3.time.DateFormatUtils;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.usermodel.Font;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.apache.poi.xssf.streaming.SXSSFCell;
- import org.apache.poi.xssf.streaming.SXSSFRow;
- import org.apache.poi.xssf.streaming.SXSSFSheet;
- import org.apache.poi.xssf.streaming.SXSSFWorkbook;
- import org.apache.poi.xssf.usermodel.XSSFRichTextString;
- /**
- * 通用的导出Excel类,(Excel 2007 OOXML (.xlsx)格式 )如果需要自定义格式的,参照此类自己再写类或方法来实现
- * dataList里的每一个Object数组一个元素(object[0])都是序号,不可放真实数据
- * @author Zhaojie
- */
- public class ExportExcelUtil2 {
- private String title; // 导出表格的表名
- private String[] rowName;// 导出表格的列名
- private List<Object[]> dataList = new ArrayList<Object[]>(); // 对象数组的List集合
- private HttpServletResponse response;
- private HttpServletRequest request;
- /**
- * 实例化导出类
- * @param title 导出表格的表名,最好是英文,中文可能出现乱码
- * @param rowName 导出表格的列名数组
- * @param dataList 对象数组的List集合
- * @param response
- */
- public ExportExcelUtil2(String title,String[] rowName,List<Object[]> dataList, HttpServletRequest request, HttpServletResponse response){
- this.title=title;
- this.rowName=rowName;
- this.dataList=dataList;
- this.response = response;
- this.request = request;
- }
- // 导出数据
- public void exportData() throws Exception{
- SXSSFWorkbook workbook = new SXSSFWorkbook();//声明一个工作薄 Excel 2007 OOXML (.xlsx)格式
- SXSSFSheet sheet = workbook.createSheet(title); // 创建表格
- for(int i = 1;i<rowName.length;i++){ //根据列名设置每一列的宽度
- int length = rowName[i].toString().length();
- sheet.setColumnWidth(i, 2*(length+1)*256);
- }
- //sheet.setDefaultRowHeightInPoints(18.5f);
- // sheet样式定义
- CellStyle columnTopStyle = this.getColumnTopStyle(workbook,14); // 头样式
- CellStyle columnStyle = this.getColumnStyle(workbook,12); // 标题样式
- CellStyle style = this.getStyle(workbook,11); // 单元格样式
- // 产生表格标题行
- sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length-1)));// 合并第一行的所有列
- SXSSFRow rowm = sheet.createRow(0); // 行
- rowm.setHeightInPoints(31f);
- SXSSFCell cellTiltle = rowm.createCell(0); // 单元格
- cellTiltle.setCellStyle(columnTopStyle);
- cellTiltle.setCellValue(title);
- // 产生第二行(列名)
- int columnNum = rowName.length; // 表格列的长度
- SXSSFRow rowRowName = sheet.createRow(1); // 在第二行创建行
- rowRowName.setHeightInPoints(21f);
- CellStyle cells = workbook.createCellStyle();
- cells.setBottomBorderColor(HSSFColor.BLACK.index);
- rowRowName.setRowStyle(cells);
- for (int i = 0; i < columnNum; i++) {
- SXSSFCell cellRowName = rowRowName.createCell(i);
- cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING); // 单元格类型
- XSSFRichTextString text = new XSSFRichTextString(rowName[i]); // 得到列的值
- cellRowName.setCellValue(text); // 设置列的值
- cellRowName.setCellStyle(columnStyle); // 样式
- }
- // 产生其它行(将数据列表设置到对应的单元格中)注意:默认添加了第一列的序号,如果不要可以注释掉
- for (int i = 0; i < dataList.size(); i++) {
- Object[] obj = dataList.get(i);//遍历每个对象
- SXSSFRow row = sheet.createRow(i+2);//创建所需的行数
- row.setHeightInPoints(17.25f);
- for (int j = 0; j < obj.length; j++) {
- SXSSFCell cell = null; //设置单元格的数据类型
- if(j==0){
- // 第一列设置为序号
- cell = row.createCell(j,SXSSFCell.CELL_TYPE_NUMERIC);
- cell.setCellValue(i+1);
- }else{
- cell = row.createCell(j,SXSSFCell.CELL_TYPE_STRING);
- if(!"".equals(obj[j]) && obj[j] != null){
- cell.setCellValue(obj[j].toString()); //设置单元格的值
- }else{
- cell.setCellValue(" ");
- }
- }
- cell.setCellStyle(style); // 样式
- }
- }
- // 让列宽随着导出的列长自动适应,但是对中文支持不是很好 也可能在linux(无图形环境的操作系统)下报错,报错再说
- // for (int i = 0; i < columnNum; i++) {
- // sheet.autoSizeColumn(i);
- // sheet.setColumnWidth(i, sheet.getColumnWidth(i)+888);//适当再宽点
- // }
- if(workbook !=null){
- // 输出到服务器上
- // FileOutputStream fileOutputStream = new FileOutputStream("D:/user.xls");
- // workbook.write(fileOutputStream);//将数据写出去
- // fileOutputStream.close();//关闭输出流
- // 输出到用户浏览器上
- OutputStream out = response.getOutputStream();
- try {
- // excel 表文件名
- String fileName = title + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
- String fileName11 = "";
- String userAgent = request.getHeader("USER-AGENT");
- if(StringUtils.contains(userAgent, "Firefox") || StringUtils.contains(userAgent, "firefox")){//火狐浏览器
- fileName11 = new String(fileName.getBytes(), "ISO8859-1");
- }else{
- fileName11 = URLEncoder.encode(fileName,"UTF-8");//其他浏览器
- }
- String headStr = "attachment; filename=\"" + fileName11 + "\"";
- response.setContentType("APPLICATION/OCTET-STREAM");
- response.setCharacterEncoding("UTF-8");
- response.setHeader("Content-Disposition", headStr);
- workbook.write(out);
- out.flush();
- workbook.close();
- workbook.dispose();
- } catch (Exception e) {
- throw e;
- } finally {
- if (null != out) {
- out.close();
- }
- }
- }
- }
- public CellStyle getColumnTopStyle(SXSSFWorkbook workbook,int fontSize) {
- // 设置字体
- Font font = workbook.createFont();
- //设置字体大小
- font.setFontHeightInPoints((short)fontSize);
- //字体加粗
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- //设置字体名字
- font.setFontName("宋体");
- //设置样式;
- CellStyle style = workbook.createCellStyle();
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(CellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- return style;
- }
- public CellStyle getColumnStyle(SXSSFWorkbook workbook,int fontSize) {
- // 设置字体
- Font font = workbook.createFont();
- //设置字体大小
- font.setFontHeightInPoints((short)fontSize);
- //字体加粗
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- //设置字体名字
- font.setFontName("宋体");
- //设置样式;
- CellStyle style = workbook.createCellStyle();
- //设置底边框;
- style.setBorderBottom(CellStyle.BORDER_THIN);
- //设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- //设置左边框;
- style.setBorderLeft(CellStyle.BORDER_THIN);
- //设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- //设置右边框;
- style.setBorderRight(CellStyle.BORDER_THIN);
- //设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- //设置顶边框;
- style.setBorderTop(CellStyle.BORDER_THIN);
- //设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(CellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- //设置背景填充色(前景色)
- style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);//设置别的颜色请去网上查询相关文档
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- return style;
- }
- public CellStyle getStyle(SXSSFWorkbook workbook,int fontSize) {
- //设置字体
- Font font = workbook.createFont();
- //设置字体大小
- font.setFontHeightInPoints((short)fontSize);
- //字体加粗
- //font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- //设置字体名字
- font.setFontName("宋体");
- //设置样式;
- CellStyle style = workbook.createCellStyle();
- //设置底边框;
- style.setBorderBottom(CellStyle.BORDER_THIN);
- //设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- //设置左边框;
- style.setBorderLeft(CellStyle.BORDER_THIN);
- //设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- //设置右边框;
- style.setBorderRight(CellStyle.BORDER_THIN);
- //设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- //设置顶边框;
- style.setBorderTop(CellStyle.BORDER_THIN);
- //设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(CellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- return style;
- }
- }