java解析excel存入map_解析excel，将解析的数据存放放到string、list、array、map等四种格式 | 学步园...

版权声明：转载请指明：杨凯专属频道

1.存放到string中的代码

package com.hudong.util;

import java.io.File;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

/**

*

* @Title: ReadExcel.java

* @Created on 2013-8-7 下午04:05:29

* @author 杨凯

*/

public class ReadExcel {

public static String readExcel(File file) {

StringBuffer sb = new StringBuffer();

Workbook wb = null;

try {

wb = Workbook.getWorkbook(file); // 构造Workbook(工作薄)对象

} catch (Exception e) {

e.printStackTrace();

}

if (wb == null) {

return null;

}

Sheet[] sheet = wb.getSheets(); // 得到Sheet(工作表)对象

if (sheet != null && sheet.length > 0) {

// 对每个工作表进行循环

for (int i = 0; i < sheet.length; i++) {

int rowNum = sheet[i].getRows(); // 得到当前工作表的行数

for (int j = 0; j < rowNum; j++) {

Cell[] cells = sheet[i].getRow(j); // 得到当前行的所有单元格

if (cells != null && cells.length > 0) {

for (int k = 0; k < cells.length; k++) { // 对每个单元格进行循环

String cellValue = cells[k].getContents(); // 读取当前单元格的值

sb.append(cellValue + "\t");

}

}

sb.append("\r\n");

}

sb.append("\r\n");

}

}

wb.close();

return sb.toString();

}

}

2.存放到List嵌套Map的代码

package com.hudong.util;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.InputStream;

import java.io.RandomAccessFile;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

/**

*

* @Title: ReadExcel.java

* @Created on 2013-8-7 下午04:05:29

* @author 杨凯

*/

public class MapReadExcel {

public static List> readExcel(File file) {

List> list = new ArrayList>();

Workbook wb = null;

try {

wb = Workbook.getWorkbook(file); // 构造Workbook(工作薄)对象

} catch (Exception e) {

e.printStackTrace();

}

if (wb == null) {

return null;

}

Sheet[] sheet = wb.getSheets(); // 得到Sheet(工作表)对象

if (sheet != null && sheet.length > 0) {

// 对每个工作表进行循环

Map map = null;

for (int i = 0; i < sheet.length; i++) {

// sheet[i].getRows()得到当前工作表的行数

for (int j = 1; j < sheet[i].getRows(); j++) {

Cell[] cells = sheet[i].getRow(j); // 得到当前行的所有单元格

if (cells != null && cells.length > 0) {

map = new HashMap(); // 读取当前单元格的值

for (int k = 0; k < cells.length; k++) { // 对每个单元格进行循环

String cellValue = cells[k].getContents();

map.put(k,cellValue);

}

}

list.add(map);

}

}

}

wb.close();

return list;

}

public static void main(String [] args){

File file = new File("e:/合作医院.xls");

List> lists = MapReadExcel.readExcel(file);

StringBuilder sb = new StringBuilder();

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

Map maps = lists.get(i);

String strSql = "insert into t_wiki_app_iframe(type_id,product_code,object_id,iframe_value,iframe_state)values(3,'doc','"+maps.get(1)+"','"+maps.get(2)+"',1);";

sb.append(strSql + "\r\n");

}

File outFile = new File("e:/t_wiki_app_iframe.txt");

try {

RandomAccessFile raf = new RandomAccessFile(outFile, "rw");

String str = new String(sb.toString().getBytes("utf-8"),"ISO-8859-1");

raf.writeBytes(str);

} catch (Exception e) {

e.printStackTrace();

}

}

}

3.存放到list嵌套数组的代码

package com.hudong.util;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

/**

*

* @Title: ExcelReader.java

* @Created on 2013-8-8 上午11:22:11

* @author 杨凯

*/

public class ExcelReader {

public static ArrayList readExcel(File excelFile) throws BiffException, IOException {

ArrayList list = new ArrayList();

Workbook rwb = null;

Cell cell = null;

InputStream stream = new FileInputStream(excelFile);

rwb = Workbook.getWorkbook(stream); // 获取Excel文件对象

Sheet sheet = rwb.getSheet(0); // 获取文件的指定工作表 默认的第一个

for (int i = 1; i < sheet.getRows(); i++) { // 行数(表头的目录不需要，从1开始)

String[] str = new String[sheet.getColumns()]; // 创建一个数组 用来存储每一列的值

for (int j = 0; j < sheet.getColumns(); j++) { // 列数

cell = sheet.getCell(j, i);

str[j] = cell.getContents(); //当前i行、当前j列对应的单元格内容，赋值给当前一维数组的当前j数据元素

}

list.add(str);

}

return list;

}

public static void main(String[] args) {

String excelFileName = "E:/月总结.xls";

try {

ArrayList list = ExcelReader.readExcel(new File(excelFileName));

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

String[] str = (String[])list.get(i); //读取Excel中的当前第i行数据； list中的每一行就是一个一维数组；

for (int j = 0; j < str.length; j++) {

System.out.print(str[j]);

}

System.out.println();

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

4.讲数据存放到map

package com.hudong.util;

import java.io.File;

import java.io.RandomAccessFile;

import java.util.HashMap;

import java.util.Iterator;

import java.util.Map;

import java.util.Map.Entry;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

/**

*

* @Title: ReadExcel.java

* @Created on 2013-8-7 下午04:05:29

* @author 杨凯

*

* 使用map返回值，可以达到解析excel的时候去重复值

*/

public class MapReadExcel {

public static Map readExcel(File file) {

Map map = new HashMap(); // 读取当前单元格的值;

Workbook wb = null;

try {

wb = Workbook.getWorkbook(file); // 构造Workbook(工作薄)对象

} catch (Exception e) {

e.printStackTrace();

}

if (wb == null) {

return null;

}

Sheet[] sheet = wb.getSheets(); // 得到Sheet(工作表)对象

if (sheet != null && sheet.length > 0) {

// 对每个工作表进行循环

for (int i = 0; i < sheet.length; i++) {

// sheet[i].getRows()得到当前工作表的行数

for (int j = 1; j < sheet[i].getRows(); j++) {

Cell[] cells = sheet[i].getRow(j); // 得到当前行的所有单元格

if (cells != null && cells.length > 0) {

map.put(cells[1].getContents(), cells[2].getContents());

}

}

}

}

wb.close();

return map;

}

public static void main(String[] args) {

File file = new File("e:/合作医院.xls");

Map map = MapReadExcel.readExcel(file);

StringBuilder sb = new StringBuilder();

Iterator it = map.entrySet().iterator();

while (it.hasNext()) {

Map.Entry maps = (Entry) it.next();

String strSql = "insert into t_wiki_app_iframe(type_id,product_code,object_id,iframe_value,iframe_state)values(3,'doc','"+maps.getKey()+"','"+maps.getValue()+"',1);";

//String strSql = "insert into t_object_apps(objid,productcode,appcode,blockcode,state,createdtime)values('" + maps.getKey() + "','doc','39yiyuan','39yiyuan',1,'2013-08-08 14:06:31');";

sb.append(strSql + "\r\n");

}

File outFile = new File("e:/t_wiki_app_iframe.txt");

//File outFile = new File("e:/t_object_apps.txt");

try {

RandomAccessFile raf = new RandomAccessFile(outFile, "rw");

String str = new String(sb.toString().getBytes("utf-8"), "ISO-8859-1");

raf.writeBytes(str);

} catch (Exception e) {

e.printStackTrace();

}

}

}