java读写excel_java实现读写excel

package com.ninemax.utils;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.ninemax.entity.ExcelVo;

import com.ninemax.test.DBCenter;

/**

* 操作Excel表格的功能类

* @author：Fuxing

* @version 1.0

*/

public class ExcelReader {

private POIFSFileSystem fs;

private HSSFWorkbook wb;

private HSSFSheet sheet;

private HSSFRow row;

/**

* 读取Excel表格表头的内容

* @param InputStream

* @return String 表头内容的数组

*

*/

public String[] readExcelTitle(InputStream is) {

try {

fs = new POIFSFileSystem(is);

wb = new HSSFWorkbook(fs);

} catch (IOException e) {

e.printStackTrace();

}

sheet = wb.getSheetAt(0);

row = sheet.getRow(0);

//标题总列数

int colNum = row.getPhysicalNumberOfCells();

String[] title = new String[colNum];

for (int i=0; i

title[i] = getStringCellValue(row.getCell((short) i));

}

return title;

}

/**

* 读取Excel数据内容

* @param InputStream

* @return Map 包含单元格数据内容的Map对象

*/

public Map readExcelContent(InputStream is) {

Map content = new HashMap();

String str = "";

try {

fs = new POIFSFileSystem(is);

wb = new HSSFWorkbook(fs);

} catch (IOException e) {

e.printStackTrace();

}

sheet = wb.getSheetAt(0);

//得到总行数

int rowNum = sheet.getLastRowNum();

row = sheet.getRow(0);

int colNum = row.getPhysicalNumberOfCells();

//正文内容应该从第二行开始,第一行为表头的标题

for (int i = 1; i <= rowNum; i++) {

row = sheet.getRow(i);

int j = 0;

while (j

//每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据

//也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean

str += getStringCellValue(row.getCell((short) j)).trim() + "-";

j ++;

}

content.put(i, str);

str = "";

}

return content;

}

/**

* 获取单元格数据内容为字符串类型的数据

* @param cell Excel单元格

* @return String 单元格数据内容

*/

private String getStringCellValue(HSSFCell cell) {

String strCell = "";

if(cell!=null){

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:

strCell = cell.getStringCellValue();

break;

case HSSFCell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case HSSFCell.CELL_TYPE_BLANK:

strCell = "";

break;

default:

strCell = "";

break;

}

if (strCell.equals("") || strCell == null) {

return "";

}

if (cell == null) {

return "";

}

}

return strCell;

}

/**

* 获取单元格数据内容为日期类型的数据

* @param cell Excel单元格

* @return String 单元格数据内容

*/

private String getDateCellValue(HSSFCell cell) {

String result = "";

try {

int cellType = cell.getCellType();

if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {

Date date = cell.getDateCellValue();

result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)

+ "-" + date.getDate();

} else if (cellType == HSSFCell.CELL_TYPE_STRING) {

String date = getStringCellValue(cell);

result = date.replaceAll("[年月]", "-").replace("日", "").trim();

} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {

result = "";

}

} catch (Exception e) {

System.out.println("日期格式不正确!");

e.printStackTrace();

}

return result;

}

public static void main(String[] args) {

try {

//对读取Excel表格标题测试

InputStream is = new FileInputStream("C:/Users/ninemax/Documents/Tencent Files/1061076658/FileRecv/人口孕检 - 副本.xls");

ExcelReader excelReader = new ExcelReader();

String[] title = excelReader.readExcelTitle(is);

System.out.println("获得Excel表格的标题:");

for (String s : title) {

System.out.print(s + " ");

}

//对读取Excel表格内容测试

InputStream is2 = new FileInputStream("C:/Users/ninemax/Documents/Tencent Files/1061076658/FileRecv/人口孕检 - 副本.xls");

Map map = excelReader.readExcelContent(is2);

System.out.println();

System.out.println("获得Excel表格的内容:");

for (int i=1; i<=map.size(); i++) {

System.out.println(map.get(i).replace("-", " "));

}

} catch (FileNotFoundException e) {

System.out.println("未找到指定路径的文件!");

e.printStackTrace();

}

//往excel中写入内容

List vos=new ArrayList();

vos=getData();

WriterDataToExcel(vos);

}

private static List getData() {

List vos=new ArrayList();

Connection conn=DBCenter.getConnection();

try {

Statement st=conn.createStatement();

String sql="select top(5000) a.sid as sid,b.ti as title,b.kw as keyword,b.ab as abstract,a.phrase as phrase,a.wxtype as wxtype,a.startpos as startpos,a.length as length,a.sentence as sentence,a.total_sentence as total_sentence from dbo.zdbz_yike_phrase_metamap_ts3 as a join nstl_test.dbo.pb18001949_test as b on a.sid=b.id order by sid asc";

ResultSet rs=st.executeQuery(sql);

while(rs.next()){

ExcelVo vo=new ExcelVo();

vo.setSid(rs.getString(1));

vo.setTitle(rs.getString("title"));

vo.setKeyword(rs.getString("keyword"));

vo.setAbstrat(rs.getString("abstract"));

vo.setPhrase(rs.getString("phrase"));

vo.setWxtype(rs.getString("wxtype"));

vo.setStartpos(rs.getInt("startpos"));

vo.setLength(rs.getInt("length"));

vo.setSentence(rs.getInt("sentence"));

vo.setTotal_sentence(rs.getInt("total_sentence"));

vos.add(vo);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return vos;

}

public static void WriterDataToExcel(List vos){

String outputFile="D:\\test.xls";

try{

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet();

// 创建新的Excel 工作簿

for(int i=0;i

//    for(int j=0;j<10;j++){

// 在索引0的位置创建行(最顶端的行)

HSSFRow row = sheet.createRow((short)i);

HSSFCell cell = row.createCell(0);

HSSFCell cell1 = row.createCell(1);

HSSFCell cell2 = row.createCell(2);

HSSFCell cell3 = row.createCell(3);

HSSFCell cell4 = row.createCell(4);

HSSFCell cell5 = row.createCell(5);

HSSFCell cell6 = row.createCell(6);

HSSFCell cell7 = row.createCell(7);

HSSFCell cell8 = row.createCell(8);

HSSFCell cell9 = row.createCell(9);

//        // 定义单元格为字符串类型

cell.setCellValue(vos.get(i).getSid());

cell1.setCellValue(vos.get(i).getTitle());

if(vos.get(i).getKeyword()==null){

cell2.setCellValue("NULL");

}else{

cell2.setCellValue(vos.get(i).getKeyword());

}

cell3.setCellValue(vos.get(i).getAbstrat());

cell4.setCellValue(vos.get(i).getPhrase());

cell5.setCellValue(vos.get(i).getWxtype());

cell6.setCellValue(vos.get(i).getStartpos());

cell7.setCellValue(vos.get(i).getLength());

cell8.setCellValue(vos.get(i).getSentence());

cell9.setCellValue(vos.get(i).getTotal_sentence());

//        // 在单元格中输入一些内容

//        if(j==0){

//        cell.setCellValue(vos.get(i).getSid());

//        }

//        if(j==1){

//        cell.setCellValue(vos.get(i).getTitle());

//        }

//        if(j==2){

//        cell.setCellValue(vos.get(i).getKeyword());

//        }

//        if(j==3){

//        cell.setCellValue(vos.get(i).getAbstrat());

//        }

//        if(j==4){

//        cell.setCellValue(vos.get(i).getPhrase());

//        }

//        if(j==5){

//        cell.setCellValue(vos.get(i).getWxtype());

//        }

//        if(j==6){

//        cell.setCellValue(vos.get(i).getStartpos());

//        }

//        if(j==7){

//        cell.setCellValue(vos.get(i).getLength());

//        }

//        if(j==8){

//        cell.setCellValue(vos.get(i).getSentence());

//        }

//        if(j==9){

//        cell.setCellValue(vos.get(i).getTotal_sentence());

//        }

// 把相应的Excel 工作簿存盘

FileOutputStream fOut = new FileOutputStream(outputFile);

workbook.write(fOut);

fOut.flush();

// 操作结束，关闭文件

fOut.close();

//    }

}

//    }

System.out.println("文件生成...");

}catch(Exception e){

e.printStackTrace();

}

//    }

}

}