java读取Excel指定列的数据到并写入新的Excel中 java读取Excel指定列的数据到并写入新的Excel中,指定的列能够这样定义: Sting rowName=“姓名”,同时满足读取所有带有“姓名”的列的数据,例如 用户姓名,客户姓名,希望谁用jxl 给个 例子参考 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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的到是有一个 二楼的你在哪抄的?你只实现excel和楼主需求不一样啊 印象中jxl是没有直接的函数符合你的需求的。可以考虑如下方式:1、先读列头,然后记下列头中内容包含姓名的列的序号(i,j,k...)2、然后遍历每一行,cell = sheet.getRow(rowNum),再获取该行相应单元格的值 cell[i],cell[j],cell[k]...3、写excel 我先前也问过类似的问题,中间犯了过很弱的错误:先确定下你的机子上装的是2003还是2007的OFFICE。确定后才再选择用POI的哪个类(HSSF还是XSSF)。你的问题:知道怎么去遍历xls文件后,再加上判断语句就可以了。我这给个我当时刚刚开始做时的一个例子,授尔于鱼,不如授尔于渔。看看这个网站:http://poi.apache.org/spreadsheet/quick-guide.htmlimport 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()); } }} 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你就自己写吧,我还不懂你的意思,怎么去写,反正应该很容易的,我写过的!~ up 可以用坐标的方式读取Excel的每个单元格吗 如何修改fillrect和clearrect在快速拖动的时候出现间断的问题? DataOutStream的简单问题 java中的反射 Jdk 1.5和Jdk 5.0有什么区别啊? 调查:(耽误您不到一分钟的时间,我们学JAVA的讨论一下)你们用JAVA都做什么东西? 关于String,byte之间的问题 YuLimin(阿敏当兵) 100一次 谁知道那里下载Visual Age for java的最新版本。 如何修改本地html文件的内容 有关String字符增加长度的过程中产生了多少个对象?JVM中是怎么实现的 菜鸟求助~~~~~~急!!!!!! java写的软件能绑定物理PC的硬件码
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的到是有一个
你只实现excel
和楼主需求不一样啊
可以考虑如下方式:1、先读列头,然后记下列头中内容包含姓名的列的序号(i,j,k...)
2、然后遍历每一行,cell = sheet.getRow(rowNum),再获取该行相应单元格的值
cell[i],cell[j],cell[k]...
3、写excel
确定后才再选择用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());
}
}
}
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你就自己写吧,我还不懂你的意思,怎么去写,反正应该很容易的,我写过的!~