java解析Excel表格数据

我们使用的包为jxl.jar，可以实现从Excel文件中读取指定的行和列的值。

java代码、Excel表格和jar包已经提交到Git上：https://github.com/buder-cp/base_component_learn/tree/master/get_excel_values

Excel表格：

java代码演示：

1.获取表格中name列的值效果：

[game_loginwith, game_guest, game_policy, game_loading, game_main_played, game_main_pkgames, game_main_battleroom, game_main_roombutton, game_main_gamebutton, game_main_instantgame, game_main_moregames, game_main_bottom, game_main_loadfail, game_main_playbtn, game_matching, game_waitingtime, game_matchsucceed, game_matchfailed, game_matchtryagain, game_resultwin, game_resultlose, game_resultagain, game_resultchange, game_result_bubble, game_result_askbutton, game_result_acceptbtn, game_result_leftbtn, game_neterror, game_retrybtn, game_timeout, game_battle_open, game_battle_enter, game_battle_roomtitle, game_battle_choose, game_create_slogan, game_room_num, game_room_waiting, game_room_getout, game_room_outbtn, game_room_staybtn, game_enter_title, game_enter_tip, game_enter_invalid, game_battle_slogan, game_enter_success, game_banner_setting, game_banner_share, game_banner_login, game_feedback_title, game_feedback_question, game_queston_bug, game_question_advice, game_question_others, game_question_detail, game_contact_info, game_contact_tip, game_submit_btn, game_submit_tip, game_contact_email, game_contact_whats, game_contact_messenger, game_contact_line, game_contact_way, game_ok_btn, game_cancel_btn, game_setting_policy, game_aboutus, game_logout]

2.获取表格中第一行的值：

[Login With, 登錄方式, 登录方式, , , , , , , , , , , , , , , , , , , , ]

3.读取表格行列的值：

行：69
列：24
name EN rev zh-rTW zh-rCN removed duplicate duplicate_name Owner 使用场景或功能名称 字符限制 order              
game_loginwith Login With 登錄方式 登录方式                     
game_guest Guest 遊客 游客                     
game_policy By signing up, you agree to our User Agreement & Privacy Policy 登錄即同意用戶協議與隱私 登录即同意用户协议与隐私                     
game_loading Loading... 加載中 加载中                     
game_main_played Recently Played Games 最近玩過的遊戲 最近玩过的游戏                     
game_main_pkgames 1V1 Games 1V1對戰遊戲 1V1对战游戏                     
game_main_battleroom Battle Rooms 對戰間 对战间                     
game_main_roombutton GO NOW Go Now Go Now                     
game_main_gamebutton PLAY NOW 立即開始 立即开始                     
game_main_instantgame Instant Games 單機遊戲 单机游戏                     
game_main_moregames More 更多遊戲 更多游戏                     
game_main_bottom Stay tuned 敬請期待 敬请期待                     
game_main_loadfail Loading failed, please retry. 遊戲加載失敗，請重試 游戏加载失败，请重试                     
game_main_playbtn PLAY 開始遊戲 开始游戏                     
game_matching Matching 匹配中 匹配中                     
game_waitingtime Waiting for %s S 已等待%s 秒 已等待%s 秒                     
game_matchsucceed Succesfully Matched 匹配成功 匹配成功                     
game_matchfailed Matching Failed 匹配失敗 匹配失败               

4.将获取到的值写入到TXT或者xls中

如下，即是全部的java代码，复制可用。

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class Excel {
	public static void main(String[] args) {
		try {
			
			//获取指定列的值
			readSpecifyColumns(new File("D:\\wenan\\111.xls"));
			
			//获取指定行的值
			readSpecifyRows(new File("D:\\wenan\\111.xls"));
			
			//读取行列的值
			readRowsAndColums(new File("D:\\wenan\\111.xls"));
			
			//将获取到的值写入到TXT或者xls中
			copy_excel(new File("D:\\wenan\\111.xls"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
     *  	读取指定列
     * @param file
     * @throws Exception
     */
	public static void readSpecifyColumns(File file)throws Exception{
		ArrayList<String> columnList = new ArrayList<String>();
		ArrayList<String> valueList = new ArrayList<String>();
		Workbook readwb = null;
		InputStream io = new FileInputStream(file.getAbsoluteFile());
		readwb = Workbook.getWorkbook(io);
		Sheet readsheet = readwb.getSheet(0);
		int rsColumns = readsheet.getColumns();  //获取表格列数
		int rsRows = readsheet.getRows();  //获取表格行数
		for (int i = 1; i < rsRows; i++) {
			Cell cell_name = readsheet.getCell(0, i);  //第一列的值
			columnList.add(cell_name.getContents());
			Cell cell_value = readsheet.getCell(2, i);  //第三列的值，此处需要手动更改，获取不同列的值
			valueList.add(cell_value.getContents());
		}
		System.out.println(columnList);
		System.out.println(valueList);
		
		String[] name_String = new String[columnList.size()];
		String[] value_String = new String[columnList.size()];
		for (int i = 0; i < columnList.size(); i++) {
			name_String[i] = columnList.get(i);
			value_String[i] = valueList.get(i);
//			System.out.println("<string name=" + "\"" + name_String[i] + "\">" + value_String[i] +  "</string>");
		}		
	}
	
	/**
	 *   	读取指定行
	 * @param file
	 * @throws Exception
	 */
	public static void readSpecifyRows(File file)throws Exception{
		ArrayList<String> columnList = new ArrayList<String>();
		Workbook readwb = null;
		InputStream io = new FileInputStream(file.getAbsoluteFile());
		readwb = Workbook.getWorkbook(io);
		Sheet readsheet = readwb.getSheet(0);
		int rsColumns = readsheet.getColumns();  //获取表格列数
		int rsRows = readsheet.getRows();  //获取表格行数
		for (int i = 1; i < rsColumns; i++) {
			Cell cell_name = readsheet.getCell(i, 1);  //在这里指定行，此处需要手动更改，获取不同行的值
			columnList.add(cell_name.getContents());
		}
		System.out.println(columnList);
	}
	
	
	private static void readRowsAndColums(File file) throws BiffException, IOException {
		//1:创建workbook
        Workbook workbook=Workbook.getWorkbook(new File("D:\\wenan\\111.xls")); 
        //2:获取第一个工作表sheet
        Sheet sheet=workbook.getSheet(0);
        //3:获取数据
        System.out.println("行："+sheet.getRows());
        System.out.println("列："+sheet.getColumns());
        for(int i=0;i<sheet.getRows();i++){
            for(int j=0;j<sheet.getColumns();j++){
                Cell cell=sheet.getCell(j,i);
                System.out.print(cell.getContents()+" ");
            }
            System.out.println();
        }
        
        //最后一步：关闭资源
        workbook.close();
	}
	
	/**
	 * 	将获取到的值写入到TXT或者xls中
	 * @param file
	 * @throws Exception
	 */
	public static void copy_excel(File file) throws Exception {
		FileWriter fWriter = null;
		PrintWriter out = null;
		String fliename = file.getName().replace(".xls", "");
		fWriter = new FileWriter(file.getParent()+ "/agetwo.xls");//输出格式为.xls
		fWriter = new FileWriter(file.getParent() + "/" + fliename + ".txt");//输出格式为.txt
		out = new PrintWriter(fWriter);
		InputStream is = new FileInputStream(file.getAbsoluteFile());
		Workbook wb = null;
		wb = Workbook.getWorkbook(is);
		int sheet_size = wb.getNumberOfSheets();
		Sheet sheet = wb.getSheet(0);
		for (int j = 1; j < sheet.getRows(); j++) {
			String cellinfo = sheet.getCell(0, j).getContents();//读取的是第二列数据，没有标题，标题起始位置在for循环中定义
			out.println(cellinfo);
		}
		out.close();//关闭流
		fWriter.close();
		out.flush();//刷新缓存
		System.out.println("输出完成！");
	}

}

java代码、Excel表格和jar包已经提交到Git上：https://github.com/buder-cp/base_component_learn/tree/master/get_excel_values