java读取Excel指定列的数据到并写入新的Excel中

[lujuzhi1202]

java读取Excel指定列的数据到并写入新的Excel中，指定的列能够这样定义： Sting rowName=“姓名”，同时满足读取所有带有“姓名”的列的数据，例如 用户姓名，客户姓名，希望谁用jxl 给个 例子参考

[DJBCAINIAO]

import java.io.ByteArrayInputStream;
//import java.io.File;
//import java.io.FileInputStream;
//import java.io.FileNotFoundException;
//import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
//import java.io.OutputStream;
import java.util.List;

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;
import org.apache.commons.io.output.ByteArrayOutputStream;
//import org.apache.commons.lang.RandomStringUtils;

import com.test.bean.User;
import com.test.dao.UserDAO;
import com.test.service.UserService;
//import com.test.util.CharacterUtil;

public class UserServiceImpl implements UserService
{
	private UserDAO userDao;

	public UserDAO getUserDao()
	{
		return userDao;
	}

	public void setUserDao(UserDAO userDao)
	{
		this.userDao = userDao;
	}

	public void delete(User user)
	{
		this.userDao.removeUser(user);
	
	}

	public List<User> findAll()
	{
		return this.userDao.findAllUsers();
	}

	public User findById(Integer id)
	{
		return this.userDao.findUserById(id);
	}

	public void save(User user)
	{
		this.userDao.saveUser(user);
	}

	public void update(User user)
	{
		this.userDao.updateUser(user);
	}

/**
 * 第一种生成方案
 
	public InputStream getInputStream()
	{
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");

		HSSFRow row = sheet.createRow(0);

		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("序号");

		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("姓");

		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("名");

		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("年龄");

		List<User> list = this.findAll();

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

			row = sheet.createRow(i + 1);

			cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(i + 1);

			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getFirstName());

			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getLastName());

			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getAge());
		}
//		生成文件名
		String fileName = RandomStringUtils.randomAlphanumeric(10);
//		String fileName = CharacterUtil.getRandomString(10);
//		fileName = new StringBuffer(fileName).append(".xls").toString();
		fileName = fileName+".xls";
//		File file = new File("test.xls");
		

		final File file = new File(fileName);

		try
		{
			OutputStream os = new FileOutputStream(file);
			wb.write(os);
			os.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}

		InputStream is = null;
		try
		{
			is = new FileInputStream(file);
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		
		new Thread(new Runnable(){
			
			public void run(){
				
				try {
					Thread.sleep(1500);
				} catch (InterruptedException e) {
					// TODO A uto-generated catch block
					e.printStackTrace();
				}
				file.delete();
			}
		}
		
		).start();

		return is;

	}
	*/
	
	 
	public InputStream getInputStream()
	{
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");

		HSSFRow row = sheet.createRow(0);

		HSSFCell cell = row.createCell((short) 0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("序号");

		cell = row.createCell((short) 1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("姓");

		cell = row.createCell((short) 2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("名");

		cell = row.createCell((short) 3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("年龄");

		List<User> list = this.findAll();

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

			row = sheet.createRow(i + 1);

			cell = row.createCell((short) 0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(i + 1);

			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getFirstName());

			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getLastName());

			cell = row.createCell((short) 3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getAge());
		}
		
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		
		try {
			
			wb.write(os);
			
		} catch (IOException e) {
			
			e.printStackTrace();
		}
		byte[] context = os.toByteArray();
		
		InputStream is = new ByteArrayInputStream(context);
		
		return is;
		
		
//
//		byte[] context = wb.getBytes();
//		
//		InputStream is = new ByteArrayInputStream(context);
//		
//		return is;

	}
}

jxl不明白 java的到是有一个

[lgm277531070]

mark

[网站推广优化yetaoaiueo]

二楼的你在哪抄的？
你只实现excel


和楼主需求不一样啊

[logon29]

印象中jxl是没有直接的函数符合你的需求的。
可以考虑如下方式：

1、先读列头，然后记下列头中内容包含姓名的列的序号（i,j,k...）
2、然后遍历每一行，cell = sheet.getRow(rowNum),再获取该行相应单元格的值
cell[i],cell[j],cell[k]...
3、写excel

[hy158753228]

我先前也问过类似的问题，中间犯了过很弱的错误：先确定下你的机子上装的是2003还是2007的OFFICE。
确定后才再选择用POI的哪个类（HSSF还是XSSF）。你的问题：知道怎么去遍历xls文件后，再加上判断语句就可以了。我这给个我当时刚刚开始做时的一个例子，授尔于鱼，不如授尔于渔。看看这个网站：http://poi.apache.org/spreadsheet/quick-guide.html

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.util.*;

public class TestPoi {
	public static void main (String [] args) {		
		try {
			String fis = "E://JAVA//MyJava//JAVAProgram//2009//Sheet.xlsx";
			Workbook wb = new XSSFWorkbook(fis);
			
			Sheet sheet1 = wb.getSheetAt(0);
		
			for (Row row : sheet1) {
				for (Cell cell : row) {
					CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
					System.out.print(cellRef.formatAsString());
					System.out.print(" - ");
					
					switch(cell.getCellType()) {
				  case Cell.CELL_TYPE_STRING:
					System.out.println(cell.getRichStringCellValue().getString());
					break;
				  case Cell.CELL_TYPE_NUMERIC:
					if(DateUtil.isCellDateFormatted(cell)) {
					  System.out.println(cell.getDateCellValue());
					} else {
					  System.out.println(cell.getNumericCellValue());
					}
					break;
				  case Cell.CELL_TYPE_BOOLEAN:
					System.out.println(cell.getBooleanCellValue());
					break;
				  case Cell.CELL_TYPE_FORMULA:
					System.out.println(cell.getCellFormula());
					break;
				  default:
					System.out.println();
					}
		    }
	    }
			
		}
		catch(IOException e) {
			System.out.println("File read error !"+e.toString());	
		}
				
	}
}

[zhaoyangdj]

学习学习

[v05120s]

private void readExcelSaveToTable(InputStream input) {  
		Workbook wb = null;  //InputStream input = multipart.getFile("file").getInputStream(); input流为你页面file流
		try {
			wb = Workbook.getWorkbook(input);
		} catch (Exception e) {
			
		}
		if (wb != null) {
			Sheet[] sheet = wb.getSheets();
			if (sheet != null && sheet.length > 0) {
				for (int i = 0, len = sheet.length; i < len; i++) {
					Sheet st = sheet[i];
					int rowNum = st.getRows(); // 行数;
					int colNum = st.getColumns(); // 列数;
					String strValue = "";
					for (int j = 0; j < rowNum; j++) {  
						for (int k = 0; k < colNum; k++) {
						Cell cell = st.getCell(k, j);
						strValue = cell.getContents();
						//在这判断strValue值是不是你想要的，如是则放到你那个新的excel中
						}
					}
				}
			}
		}
	}

至于生成新的excel你就自己写吧，我还不懂你的意思，怎么去写，反正应该很容易的，我写过的!~

[liwenso]

友情UP

[sforiz]

UP,不可以用正则吗

[八叔]

up 可以用坐标的方式读取Excel的每个单元格吗