java_method_readFile读取文件文本xls

  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 }
posted on 2017-01-09 14:59 悲伤穿透眼眸阅读(223) 评论(0) 编辑收藏举报