您的位置:

java使用xssfworkbook读取excel数据,sxssfworkbook读取excel

本文目录一览:

java怎么读取excel数据

引入poi的jar包,大致如下:

读取代码如下,应该能看得明白吧

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.math.BigDecimal;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil2007 {

/**读取excel文件流的指定索引的sheet

 * @param inputStream excel文件流

 * @param sheetIndex 要读取的sheet的索引

 * @return

 * @throws FileNotFoundException

 * @throws IOException

 */

public static XSSFSheet readExcel(InputStream inputStream,int sheetIndex) throws FileNotFoundException, IOException

{

return readExcel(inputStream).getSheetAt(sheetIndex);

}

/**读取excel文件的指定索引的sheet

 * @param filePath excel文件路径

 * @param sheetIndex 要读取的sheet的索引

 * @return

 * @throws IOException 

 * @throws FileNotFoundException 

 */

public static XSSFSheet readExcel(String filePath,int sheetIndex) throws FileNotFoundException, IOException

{

return readExcel(filePath).getSheetAt(sheetIndex);

}

/**读取excel文件的指定索引的sheet

 * @param filePath excel文件路径

 * @param sheetName 要读取的sheet的名称

 * @return

 * @throws IOException 

 * @throws FileNotFoundException 

 */

public static XSSFSheet readExcel(String filePath,String sheetName) throws FileNotFoundException, IOException

{

return readExcel(filePath).getSheet(sheetName);

}

/**读取excel文件,返回XSSFWorkbook对象

 * @param filePath excel文件路径

 * @return 

 * @throws FileNotFoundException

 * @throws IOException

 */

public static XSSFWorkbook readExcel(String filePath) throws FileNotFoundException, IOException

{

XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream(filePath));

return wb;

}

/**读取excel文件流,返回XSSFWorkbook对象

 * @param inputStream excel文件流

 * @return

 * @throws FileNotFoundException

 * @throws IOException

 */

public static XSSFWorkbook readExcel(InputStream inputStream) throws FileNotFoundException, IOException

{

XSSFWorkbook wb=new XSSFWorkbook(inputStream);

return wb;

}

/***读取excel中指定的单元格,并返回字符串形式的值

 * 1.数字

 * 2.字符

 * 3.公式(返回的为公式内容,非单元格的值)

 * 4.空

 * @param st 要读取的sheet对象

 * @param rowIndex 行索引

 * @param colIndex 列索引

 * @param isDate 是否要取的是日期(是则返回yyyy-MM-dd格式的字符串)

 * @return

 */

public static String getCellString(XSSFSheet st,int rowIndex,int colIndex,boolean isDate){

String s="";

XSSFRow row=st.getRow(rowIndex);

if(row == null) return "";

XSSFCell cell=row.getCell(colIndex);

if(cell == null) return "";

if (cell.getCellType() == 0) {//数字

if(isDate)s=new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());

else s = trimPointO(String.valueOf(getStringValue(cell)).trim());

}else if (cell.getCellType() == 1){//字符(excel中的空格,不是全角,也不是半角,不知道是神马,反正就是" "这个)

s=cell.getRichStringCellValue().getString().replaceAll(" ", " ").trim();

// s=cell.getStringCellValue();//07API新增,好像跟上一句一致

}

else if (cell.getCellType() == 2){//公式

s=cell.getCellFormula();

}

else if (cell.getCellType() == 3){//空

s="";

}

return s;

}

/**如果数字以 .0 结尾,则去掉.0

 * @param s

 * @return

 */

public static String trimPointO(String s) {

if (s.endsWith(".0"))

return s.substring(0, s.length() - 2);

else

return s;

}

/**处理科学计数法和百分比模式的数字单元格

 * @param cell

 * @return

 */

public static String getStringValue(XSSFCell cell) {

String sValue = null;

short dataFormat = cell.getCellStyle().getDataFormat();

double d = cell.getNumericCellValue();

BigDecimal b = new BigDecimal(Double.toString(d));

//百分比样式的

if (dataFormat == 0xa || dataFormat == 9) {

b=b.multiply(new BigDecimal(100));

//String temp=b.toPlainString();

DecimalFormat df=new DecimalFormat("0.00");//保留两位小数的百分比格式

sValue = df.format(b) + "%";

}else{

sValue = b.toPlainString();

}

return sValue;

}

}

《JAVA》编程中怎么用SXSSFWorkbook对已存在的excel操作进行写数据操作

XSSFWorkbook wb=new XSSFWorkbook(参数);中的参数是InputStream ,你直接XSSFWorkbook wb=new XSSFWorkbook(fs);就可以了。

第一步查询数据--这一步读者自行实现自己的数据查询 ListPointInfo points = null;

points = this.dao.getAllCollect(userId);

final MapString, ListPointInfo pointMap = new HashMap();

for (final PointInfo pointInfo : points) {

final String pt = pointInfo.getPointType(); if (pointMap.containsKey(pt)) {final ListPointInfo subList = pointMap.get(pt);

subList.add(pointInfo);

} else {final ListPointInfo subList = new ArrayList();subList.add(pointInfo);

pointMap.put(pt, subList

第二步:生成工作簿

final SXSSFWorkbook wb = new SXSSFWorkbook();

// 对每一种类型生成一个sheet

for (final Map.EntryString, ListPointInfo entry : pointMap.entrySet()) {

final ListPointInfo pts = entry.getValue();

// 获取每种类型的名字--作为sheet显示名称--如果不需要分sheet可忽略

String typeName = "";

if (this.dao.getTypeByTypeCode(pts.get(0).getPointType()) != null) {

typeName = this.dao.getTypeByTypeCode(pts.get(0).getPointType()).getPointTypeName();

}

final Sheet sheet = wb.createSheet(typeName);

//生成用于插入图片的容器--这个方法返回的类型在老api中不同

final Drawing patriarch = sheet.createDrawingPatriarch();

// 为sheet1生成第一行,用于放表头信息

final Row row = sheet.createRow(0);

// 第一行的第一个单元格的值

Cell cell = row.createCell((short) 0);

cell.setCellValue("详细地址");

cell = row.createCell((short) 1);

cell.setCellValue("经度");

cell = row.createCell((short) 2);

cell.setCellValue("纬度");

cell = row.createCell((short) 3);

for (int i = 0; i pts.size(); i++) {

final Row each = sheet.createRow(i + 1);

Cell infoCell = each.createCell((short) 0);

infoCell.setCellValue(pts.get(i).getAddrDetail());

infoCell = each.createCell((short) 1);

infoCell.setCellValue(pts.get(i).getX());

infoCell = each.createCell((short) 2);

infoCell.setCellValue(pts.get(i).getY());

infoCell = each.createCell((short) 3);

//查询获取图片路径信息--该步读者自定义

PointPic pic = this.dao.getPicInfoByPointId(pts.get(i).getId());

try {

if (pic != null) {

for (int k = 0; k 6; k++) {//因为有六张图片,所以循环6次

final short colNum = (short) (4+k);

infoCell = each.createCell(colNum);

BufferedImage img = null;

switch (k) {

case 0:

if (!StringUtils.isEmpty(pic.getPicOneAddr())) {

File imgFile = new File(pic.getPicOneAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 1:

if (!StringUtils.isEmpty(pic.getPicTwoAddr())) {

File imgFile = new File(pic.getPicTwoAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 2:

if (!StringUtils.isEmpty(pic.getPicThreeAddr())) {

File imgFile = new File(pic.getPicThreeAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 3:

if (!StringUtils.isEmpty(pic.getPicFourAddr())) {

File imgFile = new File(pic.getPicFourAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 4:

if (!StringUtils.isEmpty(pic.getPicFiveAddr())) {

File imgFile = new File(pic.getPicFiveAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

case 5:

if (!StringUtils.isEmpty(pic.getPicSixAddr())) {

File imgFile = new File(pic.getPicSixAddr());

img = ImageIO.read(imgFile);

imgFile = null;

}

break;

}

ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

ImageIO.write(img, "jpg", byteArrayOut);

img = null;

//设置每张图片插入位置

final XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, colNum,

i + 1, (short) (colNum + 1), i + 2);//参数为图片插入在表格的坐标,可以自行查看api研究参数

anchor.setAnchorType(0);

// 插入图片

patriarch.createPicture(anchor, wb.addPicture(

byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

byteArrayOut.close();

byteArrayOut = null;

}

pic = null;

}

} catch (final Exception e) {

e.printStackTrace();

}

}

}

final ByteArrayOutputStream os = new ByteArrayOutputStream();

try {

wb.write(os);

} catch (final IOException e) {

e.printStackTrace();

}

final byte[] content = os.toByteArray();

final String url = Var.BASE_URL+ File.separator + "output.xls";//读者自定义路径

final File file = new File(url);// Excel文件生成后存储的位置。

OutputStream fos = null;

try {

fos = new FileOutputStream(file);

fos.write(content);

os.close();

fos.close();

} catch (final Exception e) {

e.printStackTrace();

}

return url;//文件保存成功

Java POI读取Excel的时候怎么按列读取

按列读取的方法:

String pathname = "E:\\files\\title.xlsx";

File file = new File(pathname);

InputStream in = new FileInputStream(file);

//得到整个excel对象

XSSFWorkbook excel = new XSSFWorkbook(in);

//获取整个excel有多少个sheet

int sheets = excel.getNumberOfSheets();

//便利第一个sheet

MapString,String colMap = new HashMapString, String();

for(int i = 0 ; i sheets ; i++ ){

XSSFSheet sheet = excel.getSheetAt(i);

if(sheet == null){

continue;

}

int mergedRegions = sheet.getNumMergedRegions();

XSSFRow row2 = sheet.getRow(0);

MapInteger,String category = new HashMapInteger, String();

for(int j = 0 ; j mergedRegions; j++ ){

CellRangeAddress rangeAddress = sheet.getMergedRegion(j);

int firstRow = rangeAddress.getFirstColumn();

int lastRow = rangeAddress.getLastColumn();

category.put(rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()+"-"+row2.getCell(firstRow).toString());

}

//便利每一行

for( int rowNum = 1 ; rowNum = sheet.getLastRowNum() ; rowNum++ ){

System.out.println();

XSSFRow row = sheet.getRow(rowNum);

if(row == null){

continue;

}

short lastCellNum = row.getLastCellNum();

String cate = "";

Integer maxIndex = 0;

for( int col = row.getFirstCellNum() ; col lastCellNum ; col++ ){

XSSFCell cell = row.getCell(col);

if(cell == null ){

continue;

}

if("".equals(cell.toString())){

continue;

}

int columnIndex = cell.getColumnIndex();

String string = category.get(columnIndex);

if(string != null !string.equals("")){

String[] split = string.split("-");

cate = split[1];

maxIndex = Integer.parseInt(split[0]);

System.out.println(cate+"--"+cell.toString());

}else {

//如果当前便利的列编号小于等于合并单元格的结束,说明分类还是上面的分类名称

if(columnIndex=maxIndex){

System.out.println(cate+"--"+cell.toString());

}else {

System.out.println("分类未知"+"--"+cell.toString());

}

}

}

}

}

}