读取excel数据
package cn.edu.cust.demo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import jxl.*;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ReadExcel {
public static String replaceBlank(String str) {
String dest = "";
if (str!=null) {
Pattern p = pile("\\s*|\t|\r|\n");
Matcher m = p.matcher(str);
dest = m.replaceAll("");
}
return dest;
}
public static void writeExcelPOI(int x,int y,String s) {
try {
String fileName = "E://hello.xls"; //修改e盘的in.xls文件
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(fileName));
XSSFSheet xSheet = xwb.getSheetAt(0); //获取excel表的第一个sheet
XSSFCell xCell=xSheet.getRow(x).getCell(y); //获取单元格对象,这块不能向上边那两句代码那么写,不能用createXXX,用的话会只把第一列的数据改掉
xCell.setCellValue(s);//修改数据,看数据是否和字段集合中的数据匹配,不匹配使用元数据
// System.out.println(fields.get(fillSplit[0].trim()));
FileOutputStream out = new FileOutputStream(fileName);
xwb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
int i;
Sheet sheet;
Workbook book;
Cell cell1,cell2,cell3,cell4,cell5,cell6,cell7;
try {
//hello.xls为要读取的excel文件名
book= Workbook.getWorkbook(new File("E://in.xls"));
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet=book.getSheet(0);
//获取左上角的单元格
cell1=sheet.getCell(0,0);
// System.out.println("标题:"+cell1.getContents());
String content[]=new String[2000];
String content1[]=new String[2000];
i=1;
while(true)
{
try
{
//获取每一行的单元格
// cell1=sheet.getCell(0,i);//(列,行)
// cell2=sheet.getCell(1,i);
// cell3=sheet.getCell(2,i);
// cell4=sheet.getCell(3,i);
cell5=sheet.getCell(4,i);
// cell6=sheet.getCell(5,i);
cell7=sheet.getCell(7,i);
if("".equals(cell1.getContents())==true) //如果读取的数据为空
break;
System.out.println(cell5.getContents()+"\t");
String words=cell5.getContents();
String words1=cell7.getContents();
String change_words= replaceBlank(words);
String change_words1= replaceBlank(words1);
System.out.println(change_words);
content[i]=change_words;
content1[i]=change_words1;
if(i>=1999)
{
break;
}
}
catch(Exception e)
{
e.printStackTrace();
}
i++;
}
//文件D://JEtest//read.xls 不存在,若存在数据将会丢失
File file = new File("E://1//hello.xls");
//创建一个新的可写工作簿
// WritableWorkbook wb = Workbook.createWorkbook(file);
// WritableSheet sheet1 = wb.createSheet("第二页", 1);
// sheet1.addCell(new Label(0,i-1,change_words));
//文件D://JEtest//read.xls 不存在,若存在数据将会丢失
//创建一个新的可写工作簿
WritableWorkbook wb = Workbook.createWorkbook(file);
TWrite.write(wb,content);
// 写入Exel工作表
wb.write();
// 关闭Excel工作薄对象
wb.close();
book.close();
}
catch(Exception e) { }
}
}
excel操作.zip
写入excel数据
package cn.edu.cust.demo;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
*
* @author
*/
public class TWrite {
public static void write(WritableWorkbook wb,String str[]) throws WriteException {
WritableSheet wSheet = wb.createSheet("sheet1", 0);
for(int i=0;i