JAVA-POI导出Excel
工具类（需提供m_colnames：列名；m_data ：数据二维数组；m_tablename：标题名）
1 package nc.ui.pub.msg; 2 3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.FileOutputStream; 6 import java.io.IOException; 7 import java.text.SimpleDateFormat; 8 import java.util.Date; 9 10 import nc.bs.logging.Logger; 11 import nc.ui.pub.print.output.OutputJobUtils; 12 import nc.vo.jcom.lang.StringUtil; 13 import nc.vo.pub.lang.UFBoolean; 14 import nc.vo.pub.lang.UFDate; 15 16 import org.apache.poi.hssf.usermodel.HSSFCell; 17 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 18 import org.apache.poi.hssf.usermodel.HSSFFont; 19 import org.apache.poi.hssf.usermodel.HSSFRichTextString; 20 import org.apache.poi.hssf.usermodel.HSSFRow; 21 import org.apache.poi.hssf.usermodel.HSSFSheet; 22 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 23 import org.apache.poi.hssf.util.HSSFColor; 24 import org.apache.poi.hssf.util.Region; 25 import org.apache.poi.poifs.filesystem.POIFSFileSystem; 26 27 /** 28 * @Description: 单表导出Excel 需提供m_colnames：列名；m_data ：数据二维数组；m_tablename：标题名 29 * @author Devin 30 * @date 2013-1-10 下午02:54:48 31 * 32 */ 33 public class MyExcelExportUtils { 34 35 private Object[][] m_data = null; // Excel数据 36 37 private String[] m_colnames = null; // 列名 38 39 private String m_tablename = null; //页签名 40 41 HSSFWorkbook wb = null; 42 43 HSSFSheet hs = null; 44 45 private HSSFCellStyle tableCellStyle = null; //标题单元格属性 46 47 private HSSFCellStyle titleCellStyle = null; //列名单元格属性 48 49 public MyExcelExportUtils() { 50 super(); 51 } 52 53 /** 54 * 成功导出返回1; 55 * 文件读写错误返回-1; 56 * 其它返回0; 57 * @return 58 */ 59 public int exportExcelFile(){ 60 String[] result = OutputJobUtils.selSaveExcelFileName();//调用NC的选择文件路径，[0]文件路径,[1]是否已有文件存在 61 if(result == null) 62 return 0; 63 String excelFileName = result[0]; 64 boolean isAppend = result[1].equals("Y"); 65 if (StringUtil.isEmpty(excelFileName))//文件是否为空 66 return -1; 67 return createExcelFile(isAppend, excelFileName); 68 } 69 70 /** 71 * 创建Excel 72 * @param isAppend 73 * @param filePath 74 * @return 75 */ 76 public int createExcelFile(boolean isAppend,String filePath){ 77 boolean isCreNew = false; //是否需要新建 78 if(isAppend){//已存在路径 79 try { 80 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath)); 81 wb = new HSSFWorkbook(fs); 82 hs = wb.getSheetAt(0); 83 fillData2Sheet(hs);// 填充sheet内容 84 return write2File(filePath);// 把workbook写入文件 85 } catch (FileNotFoundException e) { 86 //如果文件不存在,则新建一个 87 isCreNew = true; 88 } catch (IOException e) { 89 Logger.error("IO错误:" + e.getMessage(), e); 90 } 91 }else{ 92 //如果文件不存在,则新建一个 93 isCreNew = true; 94 } 95 96 if( isCreNew = true){ 97 if(wb == null) 98 wb = new HSSFWorkbook(); // 创建工作薄 99 hs = wb.createSheet("Sheet1");// 创建页签 100 fillTable2Sheet(hs);// 填充Excel的标题 101 fillTitle2Sheet(hs);// 填充Excel的列名 102 fillData2Sheet(hs);// 填充sheet内容 103 return write2File(filePath);// 把workbook写入文件 104 } 105 return -1; 106 } 107 108 // 填充Excel的标题 109 private void fillTable2Sheet(HSSFSheet hsheet) { 110 HSSFRow row = hsheet.createRow(0); 111 row.setHeight((short) (row.getHeight() * 3));//设置行高为默认的3倍 112 hsheet.addMergedRegion(new Region(0, (short)0, 0, (short)m_colnames.length));//合并单元格 113 HSSFCell cell = row.createCell((short)0); 114 cell.setCellStyle(getTableCellStyle()); 115 if(m_tablename != null){ 116 HSSFRichTextString text = new HSSFRichTextString(m_tablename); 117 cell.setCellValue(text); 118 } 119 } 120 121 122 // 填充Excel的列名 123 private void fillTitle2Sheet(HSSFSheet hsheet) { 124 if(m_colnames != null){ 125 HSSFRow row = hsheet.createRow(1); 126 row.setHeight((short) (row.getHeight() * 2));//设置行高为默认的2倍 127 HSSFCell cell = null; 128 for(int i = 0 ; i < m_colnames.length ; i ++){ 129 cell = row.createCell((short)i); 130 cell.setCellStyle(getTitleCellStyle()); 131 if(m_colnames[i] != null){ 132 HSSFRichTextString text = new HSSFRichTextString(m_colnames[i]); 133 cell.setCellValue(text); 134 }//end if 135 }//end for 136 }//end if 137 } 138 139 // 填充sheet内容 140 private void fillData2Sheet(HSSFSheet hsheet) { 141 if(m_data != null){ 142 HSSFRow row = null; 143 HSSFCell cell = null; 144 int rowid = hsheet.getLastRowNum();//获取最后一行 145 146 for(int i = 0 ; i < m_data.length ; i ++){ 147 row = hsheet.createRow(rowid + i + 1); 148 Object[] rowObj = m_data[i]; 149 for(int j = 0 ; j < rowObj.length ; j ++){ 150 Object value = rowObj[j]; 151 String textValue = null;//判断值的类型后进行强制类型转换 152 cell = row.createCell((short)j); 153 /*------------begin 判断类型---------------*/ 154 if (value instanceof UFBoolean) { 155 boolean bValue = ((UFBoolean) value).booleanValue(); 156 textValue = "有误";//具体项目具体修改 157 if (!bValue) { 158 textValue ="无误"; 159 } 160 } else if (value instanceof UFDate) { 161 Date date = ((UFDate) value).toDate(); 162 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 163 textValue = sdf.format(date); 164 } else{ 165 textValue = value.toString(); 166 } 167 /*------------end 判断类型---------------*/ 168 if(textValue!=null){ 169 HSSFRichTextString text = new HSSFRichTextString(textValue); 170 cell.setCellValue(text); 171 }//end if 172 }//end for 173 }//end for 174 }//end if 175 } 176 177 // 得到列名的风格 178 private HSSFCellStyle getTableCellStyle() { 179 if(tableCellStyle == null){ 180 tableCellStyle = wb.createCellStyle(); 181 182 //设置单元格水平对齐类型 183 tableCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 184 tableCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 185 186 // 设置单元格字体 187 HSSFFont font = wb.createFont(); 188 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 189 font.setFontName("宋体"); 190 font.setFontHeight((short) 400); 191 tableCellStyle.setFont(font); 192 } 193 return tableCellStyle; 194 } 195 196 // 得到列名的风格 197 private HSSFCellStyle getTitleCellStyle() { 198 if(titleCellStyle == null){ 199 titleCellStyle = wb.createCellStyle(); 200 201 //设置单元格水平对齐类型 202 titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 203 titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 204 titleCellStyle.setWrapText(true);// 指定单元格自动换行 205 206 // 设置单元格背景色 207 titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 208 titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 209 210 /* 211 // 设置单无格的边框为粗体、边框颜色为黑色 212 titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 213 titleCellStyle.setBottomBorderColor(HSSFColor.BLACK.index); 214 titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 215 titleCellStyle.setLeftBorderColor(HSSFColor.BLACK.index); 216 titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 217 titleCellStyle.setRightBorderColor(HSSFColor.BLACK.index); 218 titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 219 titleCellStyle.setTopBorderColor(HSSFColor.BLACK.index); 220 */ 221 222 // 设置单元格字体 223 HSSFFont font = wb.createFont(); 224 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 225 font.setFontName("宋体"); 226 font.setFontHeight((short) 200); 227 titleCellStyle.setFont(font); 228 } 229 return titleCellStyle; 230 } 231 232 // 把workbook写入文件 233 private int write2File(String filePath) { 234 FileOutputStream fo = null; 235 try { 236 fo = new FileOutputStream(filePath); 237 wb.write(fo); 238 return 1; 239 } catch (IOException e) { 240 Logger.error("IO错误:" + e.getMessage(), e); 241 e.printStackTrace(); 242 return -1; 243 }finally{ 244 if(fo != null){ 245 try { 246 fo.close(); 247 } catch (IOException e) { 248 Logger.error("IO错误:" + e.getMessage(), e); 249 e.printStackTrace(); 250 return -1; 251 }//end catch 252 }//end if 253 }//end finally 254 } 255 256 public void setData(Object[][] m_data) { 257 this.m_data = m_data; 258 } 259 260 public void setColNames(String[] m_colnames) { 261 this.m_colnames = m_colnames; 262 } 263 264 public void setTableName(String m_tablename){ 265 this.m_tablename = m_tablename; 266 } 267 268 }
效果：
