JAVA进行Excel读写

最新推荐文章于 2016-12-14 13:52:38 发布

即刻出发

最新推荐文章于 2016-12-14 13:52:38 发布

阅读量570

点赞数

分类专栏： javaweb开发

本文链接：https://blog.csdn.net/a468903507/article/details/12869581

版权

javaweb开发专栏收录该内容

104 篇文章 0 订阅

订阅专栏

需要下载POI开源包,下载地址: http://poi.apache.org/

package com.bugmanager.operator.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.util.HSSFColor;

import com.bugmanager.operator.bean.Bug;

public class ExcelUtil {
public static int createReport(ResultSet rs,OutputStream fileOut) {
  int ret = 0;
  try {
   ResultSetMetaData rsmd = rs.getMetaData();
   // 获得数据集的列数
   int numbercols = rsmd.getColumnCount();
   rs.last();
   // 将数据集游标置为最后一行，获得数据集的行数
   int numberrows = rs.getRow();
   // 生成一个HSSFWorkbook对象，进行操作
   HSSFWorkbook wb = new HSSFWorkbook();
   // 设置显示的字体以及相关的样式
   HSSFFont font = wb.createFont();
   font.setFontHeightInPoints((short) 8);
   font.setFontName("Courier   New");
   font.setItalic(false);
   font.setStrikeout(false);

   // 设置标题栏单元格的样式
   HSSFCellStyle cellHeadStyle = wb.createCellStyle();
   cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
   cellHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
   cellHeadStyle.setBorderLeft((short) 1);
   cellHeadStyle.setBorderRight((short) 1);
   cellHeadStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
   cellHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
   cellHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   // 设置数据行的字体以及以及相关的对齐方式
   HSSFCellStyle cellDataStyle = wb.createCellStyle();
   cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   cellDataStyle.setFont(font);

// 新建一个HSSFSheet对象
HSSFSheet sheet = wb.createSheet("new sheet");

   // 处理标题行，建立单元格，得到相关表字段名称写入到相关的单元格
   HSSFRow row = sheet.createRow((short) 0);
   for (int i = 1; i <= numbercols; i++) {
    HSSFCell cellHead = row.createCell((short) (i - 1));
    cellHead.setCellStyle(cellHeadStyle);
    cellHead.setCellValue(rsmd.getColumnLabel(i));
   }
   // 重新定位数据行
   rs.beforeFirst();
   int j = 1;
   // 将每一行的数据写入到相应的单元格
   while (rs.next() && j <= numberrows) {
    HSSFRow row1 = sheet.createRow((short) j);
    for (int k = 1; k <= numbercols; k++) {
     HSSFCell cellData = row1.createCell((short) (k - 1));
     cellData.setCellStyle(cellDataStyle);
     // 设置编码类型，用于支持中文的输出
     cellData.setEncoding(HSSFCell.ENCODING_UTF_16);
     /*
      * 判断原来数据的格式，然后以相同的数据格式写入单元格，以方便用户
      * 可以进行一定的统计工作，日期类型和字符串类型使用String类型进行输出
      */
     if (rsmd.getColumnType(k) == java.sql.Types.INTEGER)
      cellData.setCellValue(rs.getInt(k));
     else if (rsmd.getColumnType(k) == java.sql.Types.VARCHAR)
      cellData.setCellValue(rs.getString(k));
     else if (rsmd.getColumnType(k) == java.sql.Types.REAL)
      cellData.setCellValue(rs.getDouble(k));
     else
      cellData.setCellValue(rs.getString(k));
    }
    j++;

   }
   // 利用输出流将数据集写入到硬盘的文件中，操作结束后，关闭输出流
//   FileOutputStream fileOut = new FileOutputStream(
//     "c:\\bugInfo.xls");
   wb.write(fileOut);
   fileOut.close();
   ret = 1;
  } catch (Exception e) {
   ret = 0;
  } finally{
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return ret;
}

public static List<Bug> readExcel(InputStream is) {
  List<Bug> list = new ArrayList<Bug>();
  try {
   // InputStream iss = ExcelUtil.class
   // .getResourceAsStream("reportExport.xls");
   POIFSFileSystem fs = new POIFSFileSystem(is);
   HSSFWorkbook wb = new HSSFWorkbook(fs);
   HSSFSheet sheet = wb.getSheetAt(0);
   // 循环每一行
   Iterator rows = sheet.rowIterator();
   Bug bug = null;
   for (HSSFRow row = (HSSFRow) rows.next(); rows.hasNext();) {
    row = (HSSFRow) rows.next();
    // System.out.println("Row #" + row.getRowNum());
    // 循环第一个单元格
    Iterator cells = row.cellIterator();
    // while (cells.hasNext()) {
    bug = new Bug();
    for (int i = 0; i < row.getLastCellNum(); i++) {
     // HSSFCell cell = (HSSFCell) cells.next();
     HSSFCell cell = row.getCell(Short.parseShort("" + i));

     switch (i) {
     case 0:
      bug.setBugId(cell.getStringCellValue());
     case 1:
      bug.setDetails(cell.getStringCellValue());
     case 2:
      bug.setState(cell.getStringCellValue());
     case 3:
      bug.setOwner(cell.getStringCellValue());
     case 4:
      bug.setComponent(cell.getStringCellValue());
     default:
//      System.out.println("unsuported sell type");
     }
     // switch (cell.getCellType()) {
     // case HSSFCell.CELL_TYPE_NUMERIC:
     // System.out.println(cell.getNumericCellValue());
     // break;
     // case HSSFCell.CELL_TYPE_STRING:
     // System.out.println(cell.getStringCellValue());
     // break;
     // default:
     // System.out.println("unsuported sell type");
     // break;
    }
    list.add(bug);
   }

//   System.out.println(list.size() + "********");

  } catch (IOException ex) {
   ex.printStackTrace();
  } finally {
   try {
    is.close();
   } catch (IOException e) {
    e.printStackTrace();
   }

}
return list;
}

public static void main(String[] args) {
  // ResultSet rs = DBUtil.getResultSet("select * from bug_operators");
  // ExcelUtil.createReport(rs);
  InputStream is = null;
  ExcelUtil.readExcel(is);
  System.out.println("ok....");
}
}