1 package cn.com.qmhd.tools; 2 3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.FileOutputStream; 6 import java.io.IOException; 7 import java.util.ArrayList; 8 import java.util.HashMap; 9 import java.util.List; 10 11 import javax.servlet.http.HttpServletRequest; 12 13 import org.apache.poi.hssf.usermodel.HSSFCell; 14 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 15 import org.apache.poi.hssf.usermodel.HSSFFont; 16 import org.apache.poi.hssf.usermodel.HSSFRow; 17 import org.apache.poi.hssf.usermodel.HSSFSheet; 18 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 19 import org.apache.poi.hssf.util.HSSFColor; 20 import org.apache.poi.poifs.filesystem.POIFSFileSystem; 21 import org.apache.poi.xssf.usermodel.XSSFCell; 22 import org.apache.poi.xssf.usermodel.XSSFRow; 23 import org.apache.poi.xssf.usermodel.XSSFSheet; 24 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 25 26 public class ExcelUtils { 27 28 public int readLine ( String filePath ){ 29 try { 30 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( filePath )); 31 HSSFWorkbook workbook = new HSSFWorkbook(fs); 32 HSSFSheet sheet = workbook.getSheetAt(0); 33 int rows = sheet.getLastRowNum()+1; 34 return rows ; 35 } catch (FileNotFoundException e) { 36 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:readLine:FileNotFound:error"); 37 return -1 ; 38 } catch (IOException e) { 39 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:readLine:IO:error"); 40 return -1 ; 41 } 42 } 43 44 public int readLine2007 ( String filePath ){ 45 try { 46 47 @SuppressWarnings("deprecation") 48 XSSFWorkbook xwb = new XSSFWorkbook(filePath); 49 XSSFSheet sheet = xwb.getSheetAt(0); 50 int rows = sheet.getLastRowNum()+1; 51 return rows ; 52 } catch (FileNotFoundException e) { 53 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:readLine:FileNotFound:error"); 54 return -1 ; 55 } catch (IOException e) { 56 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:readLine:IO:error"); 57 return -1 ; 58 } 59 } 60 61 public List< HashMap< String,String > > read( String fileName, HttpServletRequest request , int i , int line){ 62 List< HashMap< String,String > > list = new ArrayList< HashMap< String,String > >(); 63 try { 64 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( request.getRealPath("/")+"upload/"+fileName )); 65 HSSFWorkbook workbook = new HSSFWorkbook(fs); 66 HSSFSheet sheet = workbook.getSheetAt(0); 67 //int rows = sheet.getPhysicalNumberOfRows(); 68 int rows = (i+1)*1000; 69 if ( rows >= line ) { 70 rows = line; 71 } 72 for (int r = i*1000; r < rows; r++) 73 { 74 HSSFRow row = sheet.getRow(r); 75 if ( row != null ) 76 { 77 int cells = row.getLastCellNum(); 78 String value = ""; 79 HashMap< String,String > map = new HashMap< String,String >(); 80 for (short c = 0; c < cells; c++){ 81 HSSFCell cell = row.getCell(c); 82 if (cell != null){ 83 switch (cell.getCellType()) 84 { 85 case HSSFCell.CELL_TYPE_FORMULA: 86 // 87 break; 88 case HSSFCell.CELL_TYPE_NUMERIC: 89 value = (long) cell.getNumericCellValue()+""; 90 break; 91 case HSSFCell.CELL_TYPE_STRING: 92 value = cell.getStringCellValue()+""; 93 break; 94 default: 95 value = ""; 96 } 97 map.put( c+"" , value ); 98 } 99 } 100 list.add( map ); 101 } 102 } 103 return list; 104 } catch (FileNotFoundException e) { 105 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:read:FileNotFound:error"); 106 return list; 107 } catch (IOException e) { 108 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:read:IO:error"); 109 return list; 110 } 111 } 112 113 public List<String[]> read2007( String filePath , int nTotal ) { 114 115 List<String[]> list = new ArrayList<String[]>(); 116 String[] strs = null; 117 118 try { 119 120 XSSFWorkbook xwb = new XSSFWorkbook(filePath); 121 XSSFSheet sheet = xwb.getSheetAt(0); 122 int rows =nTotal; 123 124 for (int r = 0; r < rows; r++){ 125 126 XSSFRow row = sheet.getRow(r); 127 if ( row != null && row.getLastCellNum()>0){ 128 int cells = row.getLastCellNum(); 129 String value = ""; 130 strs = new String[cells]; 131 for (short c = 0; c < cells; c++){ 132 XSSFCell cell = row.getCell(c); 133 if (cell != null){ 134 switch (cell.getCellType()) 135 { 136 case XSSFCell.CELL_TYPE_FORMULA: 137 // 138 break; 139 case XSSFCell.CELL_TYPE_NUMERIC: 140 value = (long) cell.getNumericCellValue()+""; 141 break; 142 case XSSFCell.CELL_TYPE_STRING: 143 value = cell.getStringCellValue()+""; 144 break; 145 default: 146 value = ""; 147 } 148 149 strs[ Integer.parseInt( c+"" ) ] = value; 150 } 151 } 152 list.add( strs ); 153 } 154 strs = null; 155 } 156 return list; 157 } catch (FileNotFoundException e) { 158 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:read:FileNotFound:error"); 159 return null; 160 } catch (IOException e) { 161 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:read:IO:error"); 162 return null; 163 } 164 } 165 166 public List<String[]> read( String filePath , int nTotal ) { 167 168 List<String[]> list = new ArrayList<String[]>(); 169 String[] strs = null; 170 171 try { 172 POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream( filePath ) ); 173 HSSFWorkbook workbook = new HSSFWorkbook(fs); 174 HSSFSheet sheet = workbook.getSheetAt(0); 175 int rows =nTotal; 176 177 for (int r = 0; r < rows; r++){ 178 179 HSSFRow row = sheet.getRow(r); 180 if ( row != null && row.getLastCellNum()>0){ 181 int cells = row.getLastCellNum(); 182 String value = ""; 183 strs = new String[cells]; 184 for (short c = 0; c < cells; c++){ 185 HSSFCell cell = row.getCell(c); 186 if (cell != null){ 187 switch (cell.getCellType()) 188 { 189 case HSSFCell.CELL_TYPE_FORMULA: 190 // 191 break; 192 case HSSFCell.CELL_TYPE_NUMERIC: 193 value = (long) cell.getNumericCellValue()+""; 194 break; 195 case HSSFCell.CELL_TYPE_STRING: 196 value = cell.getStringCellValue()+""; 197 break; 198 default: 199 value = ""; 200 } 201 202 strs[ Integer.parseInt( c+"" ) ] = value; 203 } 204 } 205 list.add( strs ); 206 } 207 strs = null; 208 } 209 return list; 210 } catch (FileNotFoundException e) { 211 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:read:FileNotFound:error"); 212 return null; 213 } catch (IOException e) { 214 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:read:IO:error"); 215 return null; 216 } 217 } 218 219 public boolean write( String filePath, List<String[]> list ) { 220 // 新建文件 221 HSSFWorkbook wb = new HSSFWorkbook(); 222 // 新建工作表 223 HSSFSheet sheet = wb.createSheet("Sheet1"); 224 sheet.setDefaultColumnWidth((short) 20); 225 sheet.setColumnWidth((short)7, (short)20000); 226 227 HSSFCellStyle style = wb.createCellStyle(); 228 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 229 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 230 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 231 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 232 style.setBorderRight(HSSFCellStyle.BORDER_THIN); 233 style.setBorderTop(HSSFCellStyle.BORDER_THIN); 234 // 生成一个字体 235 HSSFFont font = wb.createFont(); 236 //font.setColor(HSSFColor.VIOLET.index); 237 font.setFontHeightInPoints((short) 12); 238 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 239 240 // 把字体应用到当前的样式 241 style.setFont(font); 242 for (int hang = 0; hang <list.size(); hang++) 243 { 244 // 创建行 245 HSSFRow row = sheet.createRow((short) hang); 246 String[] strs = list.get(hang); 247 for (int lie = 0; lie < strs.length; lie++) 248 { 249 250 HSSFCell cell = row.createCell((short) lie);// 创建格 createCell((short) lie) 251 252 if (hang == 0) { 253 cell.setCellStyle(style); 254 } 255 cell.setCellValue(strs[lie]); 256 } 257 } 258 259 FileOutputStream fileout; 260 try { 261 fileout = new FileOutputStream(filePath); 262 wb.write(fileout); 263 fileout.close(); 264 return true; 265 } catch (FileNotFoundException e) { 266 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:write:FileNotFound:error"); 267 } catch (IOException e) { 268 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:write:FileNotFound:error"); 269 } 270 271 return false; 272 } 273 274 public boolean write2( String filePath, String[] strs ) { 275 276 // 新建文件 277 HSSFWorkbook wb = new HSSFWorkbook(); 278 // 新建工作表 279 HSSFSheet sheet = wb.createSheet("Sheet1"); 280 sheet.setDefaultColumnWidth((short) 20); 281 282 HSSFCellStyle style = wb.createCellStyle(); 283 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 284 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 285 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 286 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 287 style.setBorderRight(HSSFCellStyle.BORDER_THIN); 288 style.setBorderTop(HSSFCellStyle.BORDER_THIN); 289 // 生成一个字体 290 HSSFFont font = wb.createFont(); 291 //font.setColor(HSSFColor.VIOLET.index); 292 font.setFontHeightInPoints((short) 12); 293 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 294 295 // 把字体应用到当前的样式 296 style.setFont(font); 297 for (int hang = 0; hang < 1; hang++) 298 { 299 HSSFRow row = sheet.createRow((short) hang); 300 int nArg = strs.length; 301 HSSFCell cell = row.createCell((short)(0)); 302 cell.setCellStyle(style); 303 cell.setCellValue( "电话号码" ); 304 for (int lie = 0; lie < nArg; lie++) 305 { 306 // 创建格 createCell((short) lie) 307 cell = row.createCell((short)(lie+1)); 308 cell.setCellStyle(style); 309 cell.setCellValue( strs[lie] ); 310 } 311 } 312 313 FileOutputStream fileout; 314 try { 315 fileout = new FileOutputStream(filePath); 316 wb.write(fileout); 317 fileout.close(); 318 return true; 319 } catch (FileNotFoundException e) { 320 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:write2:FileNotFound:error"); 321 } catch (IOException e) { 322 ExceptionHeading.getException(this.getClass().getName(), e, "ExcelUtils:write2:FileNotFound:error"); 323 } 324 325 return false; 326 } 327 328 }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步