java poi写入excel_java使用poi包将数据写入Excel表格

1 importjava.io.File;2 importjava.io.FileInputStream;3 importjava.io.FileNotFoundException;4 importjava.io.FileOutputStream;5 importjava.io.IOException;6 importjava.lang.reflect.Field;7 importjava.lang.reflect.Method;8 importjava.util.ArrayList;9 importjava.util.List;10

11 importorg.apache.poi.hssf.usermodel.HSSFRow;12 importorg.apache.poi.hssf.usermodel.HSSFSheet;13 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;14 importorg.apache.poi.ss.usermodel.Cell;15 importorg.apache.poi.ss.usermodel.Row;16 importorg.springframework.stereotype.Component;17

18 /**

19 * @Description:20 *@author21 * @date 创建时间：2016年12月8日下午2:38:4722 *@version1.023 */

24 @Component25 public classExcelManage {26 private HSSFWorkbook workbook = null;27

28 /**

29 * 判断文件是否存在30 *@paramfilePath 文件路径31 *@return

32 */

33 public booleanfileExist(String filePath){34 boolean flag = false;35 File file = newFile(filePath);36 flag =file.exists();37 returnflag;38 }39

40 /**

41 * 判断文件的sheet是否存在42 *@paramfilePath 文件路径43 *@paramsheetName 表格索引名44 *@return

45 */

46 public booleansheetExist(String filePath,String sheetName){47 boolean flag = false;48 File file = newFile(filePath);49 if(file.exists()){ //文件存在50 //创建workbook

51 try{52 workbook = new HSSFWorkbook(newFileInputStream(file));53 //添加Worksheet(不添加sheet时生成的xls文件打开时会报错)

54 HSSFSheet sheet =workbook.getSheet(sheetName);55 if(sheet!=null)56 flag = true;57 } catch(Exception e) {58 e.printStackTrace();59 }60 }else{ //文件不存在

61 flag = false;62 }63 returnflag;64 }65 /**

66 * 创建新Sheet并写入第一行数据67 *@paramfilePath excel的路径68 *@paramsheetName 要创建的表格索引69 *@paramtitleRow excel的第一行即表格头70 *@throwsIOException71 *@throwsFileNotFoundException72 */

73 public void createSheet(String filePath,String sheetName,String titleRow[]) throwsFileNotFoundException, IOException{74 FileOutputStream out = null;75 File excel = new File(filePath); //读取文件

76 FileInputStream in = new FileInputStream(excel); //转换为流

77 workbook = new HSSFWorkbook(in); //加载excel的 工作目录

78

79 workbook.createSheet(sheetName); //添加一个新的sheet80 //添加表头

81 Row row = workbook.getSheet(sheetName).createRow(0); //创建第一行

82 try{83 for(int i = 0;i < titleRow.length;i++){84 Cell cell =row.createCell(i);85 cell.setCellValue(titleRow[i]);86 }87 out = newFileOutputStream(filePath);88 workbook.write(out);89 }catch(Exception e) {90 e.printStackTrace();91 }finally{92 try{93 out.close();94 } catch(IOException e) {95 e.printStackTrace();96 }97 }98 }99/**

100 * 创建新excel.101 *@paramfilePath excel的路径102 *@paramsheetName 要创建的表格索引103 *@paramtitleRow excel的第一行即表格头104 */

105 public voidcreateExcel(String filePath,String sheetName,String titleRow[]){106 //创建workbook

107 workbook = newHSSFWorkbook();108 //添加Worksheet(不添加sheet时生成的xls文件打开时会报错)

109 workbook.createSheet(sheetName);110 //新建文件

111 FileOutputStream out = null;112 try{113 //添加表头

114 Row row = workbook.getSheet(sheetName).createRow(0); //创建第一行

115 for(int i = 0;i < titleRow.length;i++){116 Cell cell =row.createCell(i);117 cell.setCellValue(titleRow[i]);118 }119 out = newFileOutputStream(filePath);120 workbook.write(out);121 } catch(Exception e) {122 e.printStackTrace();123 } finally{124 try{125 out.close();126 } catch(IOException e) {127 e.printStackTrace();128 }129 }130 }131 /**

132 * 删除文件.133 *@paramfilePath 文件路径134 */

135 public booleandeleteExcel(String filePath){136 boolean flag = false;137 File file = newFile(filePath);138 //判断目录或文件是否存在

139 if (!file.exists()) {140 returnflag;141 } else{142 //判断是否为文件

143 if (file.isFile()) { //为文件时调用删除文件方法

144 file.delete();145 flag = true;146 }147 }148 returnflag;149 }150 /**

151 * 往excel中写入.152 *@paramfilePath 文件路径153 *@paramsheetName 表格索引154 *@paramobject155 */

156 public voidwriteToExcel(String filePath,String sheetName, Object object,String titleRow[]){157 //创建workbook

158 File file = newFile(filePath);159 try{160 workbook = new HSSFWorkbook(newFileInputStream(file));161 } catch(FileNotFoundException e) {162 e.printStackTrace();163 } catch(IOException e) {164 e.printStackTrace();165 }166 FileOutputStream out = null;167 HSSFSheet sheet =workbook.getSheet(sheetName);168 //获取表格的总行数

169 int rowCount = sheet.getLastRowNum() + 1; //需要加一

170 try{171 Row row = sheet.createRow(rowCount); //最新要添加的一行172 //通过反射获得object的字段,对应表头插入173 //获取该对象的class对象

174 Class extends Object> class_ =object.getClass();175

176 for(int i = 0;i < titleRow.length;i++){177 String title =titleRow[i];178 String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); //使其首字母大写;

179 String methodName = "get"+UTitle;180 Method method = class_.getDeclaredMethod(methodName); //设置要执行的方法

181 String data = method.invoke(object).toString(); //执行该get方法,即要插入的数据

182 Cell cell =row.createCell(i);183 cell.setCellValue(data);184 }185 out = newFileOutputStream(filePath);186 workbook.write(out);187 } catch(Exception e) {188 e.printStackTrace();189 } finally{190 try{191 out.close();192 } catch(IOException e) {193 e.printStackTrace();194 }195 }196 }197 }