需用用到poi的包,自己去下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<Map<Integer,String>> getExcelDataBySheet(HSSFSheet sheet) {
List<Map<Integer,String>> table_list = new ArrayList<Map<Integer,String>>();
int rows = sheet.getLastRowNum();
int columns = sheet.getRow(0).getLastCellNum();
//循环列数
for(int i = 0; i <= rows; i++)
{
//得到第i行
HSSFRow row = sheet.getRow(i);
Map<Integer,String> map = new HashMap<Integer,String>();
//循环第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<Map<Integer,String>> l = getExcelDataBySheet(sheet);
// int size = l.size();
// for(int i = 0; i < size; i++)
// {
// System.out.println("第"+(i+1)+"行");
// Map<Integer,String> map = (Map<Integer,String>)l.get(i);
// Set<Integer> 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");
}
}
数据导入到excel
最新推荐文章于 2024-04-16 10:42:06 发布