java excel表导入excel数据,java代码导入excel报表里的数据

Java codepackage io;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Set;

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;

public class POI_Excel {

//给定一个sheet，获取整个sheet的数据，每一行包装成一个map，key是行号，value是表格的值。然后把map加入list，这样整个sheet是一个list

public static List> getExcelDataBySheet(HSSFSheet sheet) {

List> table_list = new ArrayList>();

int rows = sheet.getLastRowNum();

int columns = sheet.getRow(0).getLastCellNum();

//循环列数

for(int i = 0; i <= rows; i++)

{

//得到第i行

HSSFRow row = sheet.getRow(i);

Map map = new HashMap();

//循环第i行的数据，从0到columns

for(int j = 0; j < columns; j++)

{

HSSFCell cell = null;

try {

//获取cell，如果报异常，说明整个row是空的null，直接在catch里面捕获，并赋值为空

cell = row.getCell(j);

} catch (NullPointerException e1) {

map.put(j, "");

continue;

}

//如果cell为空

if(null == cell)

{

map.put(j, "");

continue;

}

//获取cell的类型

int type = cell.getCellType();

//如果是空白

if(type == HSSFCell.CELL_TYPE_BLANK)

{

map.put(j, "");

}

//如果是数字型

else if(type == HSSFCell.CELL_TYPE_NUMERIC)

{

//如果cell里面包含E或者e，说明是科学计数法，要用特殊方法处理

if(String.valueOf(cell.getNumericCellValue()).matches(".*[E|e].*"))

{

DecimalFormat df = new DecimalFormat("#.#");

//指定最长的小数点位为10

df.setMaximumFractionDigits(10);

map.put(j, df.format((cell.getNumericCellValue())));

}

else

{

map.put(j, cell.getNumericCellValue()+"");

}

}

//如果是字符串

else if(type == HSSFCell.CELL_TYPE_STRING)

{

map.put(j, cell.getStringCellValue());

}

//如果是公式型

else if(type == HSSFCell.CELL_TYPE_FORMULA)

{

String value;

try {

value = cell.getRichStringCellValue().getString();

map.put(j,value);

} catch (Exception e) {

value = cell.getNumericCellValue()+"";

map.put(j, value);

}

}

else

{

map.put(j, "");

}

}

table_list.add(map);

}

return table_list;

}

//给定一个sheet和行号，列号，获取其值

public static String getExcelCellData(HSSFSheet sheet,int row,int colnum)

{

HSSFRow excel_row = sheet.getRow(row);

if(excel_row == null)

return "";

HSSFCell cell = excel_row.getCell(colnum);

if(cell == null)

return "";

if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)

return "";

else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)

return cell.getNumericCellValue()+"";

else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING)

return cell.getStringCellValue().toString();

else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)

{

String value;

try {

value = cell.getRichStringCellValue().getString();

return value;

} catch (Exception e) {

value = cell.getNumericCellValue()+"";

return value;

}

}

return null;

}

//写入excel，注意是只写了第一行

public static void writeToExcel(String sheetName,String path,Map value,String fileName) throws Exception {

HSSFSheet sheet;

HSSFRow row;

HSSFCell cell;

File dir = new File(path);

dir.mkdirs();

File file = new File(dir.getCanonicalFile() + "\\" + fileName);

if(file.exists())

file.delete();

FileOutputStream fis = new FileOutputStream(file, true);

HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();

sheet = hSSFWorkbook.createSheet(sheetName);

row = sheet.createRow(0);

Iterator l = value.keySet().iterator();

while(l.hasNext())

{

String col = (String)l.next();

String cellValue = (String)value.get(col);

cell = row.createCell(Integer.parseInt(col)-1);

cell.setCellValue(cellValue);

}

hSSFWorkbook.write(fis);

fis.close();

}

public static void main(String[] args) throws Exception, IOException {

//读取excel

HSSFWorkbook work = new HSSFWorkbook(new FileInputStream(new File("e:\\2.xls")));

HSSFSheet sheet = work.getSheetAt(0);

System.out.println(getExcelCellData(sheet, 0, 0));

// List> l = getExcelDataBySheet(sheet);

// int size = l.size();

// for(int i = 0; i < size; i++)

// {

// System.out.println("第"+(i+1)+"行");

// Map map = (Map)l.get(i);

// Set set = map.keySet();

// for(int j : set)

// {

// System.out.println("第"+(j+1)+"列:"+map.get(j));

// }

// }

//

// //把数据写入excel，注意是只写了第一行

// Map map = new HashMap();

// map.put("1","wang"); //表示第一行第一列是wang，第一行第二列是gang

// map.put("2", "ga");

// writeToExcel("wang","d:\\wang",map,"222.xls");

}

}