首页 > 代码库 > Java 实现Excel表数据的读取和写入 以及过程中可能遇到的问题
Java 实现Excel表数据的读取和写入 以及过程中可能遇到的问题
问题1:Unable to recognize OLE stream
格式的问题要可能是因为给的数据是2010年的数据表后缀为.xlsx,要先转化成2003版的后缀为.xls
问题2:
Warning: Property storage name for 5 is empty - setting to Root Entry
可能是jxl.jar 不支持Excel 5.0 for Mac,在Mac下导入会出现这个问题。
//从Excel中读取数据rowNum行
public static double[] readExcel(File excelFile,int rowNum) throws BiffException,IOException{
double[] dataX = new double[1000];
Workbook rwb = null;
Cell cell = null;
InputStream stream = new FileInputStream(excelFile);
rwb = Workbook.getWorkbook(stream);
//获取指定工作表默认为第一个
Sheet sheet = rwb.getSheet(0);
//行数,在这里读取从第二行开始读取数据
for (int i = 1; i <= rowNum; i++) {
String str = new String();
//这里选择要读取的数据的列数和行数例如(3,4)说明为第三列第四行,行数都是从0开始计数
cell = sheet.getCell(3, i);
str = cell.getContents();
dataX[i-1] = Double.parseDouble(str);
//下面就是为了打印一下数据看一下读取的数据是否正确
System.out.println("Data " + (i-1) +" : " +dataX[i-1]);
}
return dataX;
}
//将数据存储到Excel表中
public static void creatExcel(File outFileName,double[] risks) throws BiffException, IOException,WriteException {
//获得输出流
OutputStream os = new FileOutputStream(outFileName);
WritableWorkbook workbook = Workbook.createWorkbook(os);
//在这里指定你存储数据表的名称
WritableSheet sheet = workbook.createSheet("Deutsche", 1);
Label parameter = new Label(1,1,"1/阿尔法");
sheet.addCell(parameter);
for (int i = 0; i < risks.length; i++) {
Number risk = new Number(1,i+2,risks[i]);
sheet.addCell(risk);
}
//将输出流中数据写入Excel,关闭输出流
workbook.write();
workbook.close();
os.close();
}
一些表结构:::
Label formate = new Label(0,0,"数据格式");
sheet.addCell(formate);
Label floats = new Label(1,0,"浮点型");
sheet.addCell(floats);
Label integers = new Label(2,0,"整型");
sheet.addCell(integers);
Label booleans = new Label(3,0,"布尔型");
sheet.addCell(booleans);
Label dates = new Label(4,0,"日期格式");
sheet.addCell(dates);
Label example = new Label(0,1,"数据示例");
sheet.addCell(example);
//浮点数据
Number number = new Number(1,1,3.1415926535);
sheet.addCell(number);
//整形数据
Number ints = new Number(2,1,15042699);
sheet.addCell(ints);
Boolean bools = new Boolean(3,1,true);
sheet.addCell(bools);
//日期型数据
Calendar c = Calendar.getInstance();
Date date = c.getTime();
WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
DateTime dt = new DateTime(4,1,date,cf1);
sheet.addCell(dt);
}
//复杂布局以及样式:::
public class MutiStyleExcelWrite {
public void createExcel(OutputStream os) throws WriteException,IOException {
//创建工作薄
WritableWorkbook workbook = Workbook.createWorkbook(os);
//创建新的一页
WritableSheet sheet = workbook.createSheet("First Sheet", 0);
//构造表头
sheet.mergeCells(0, 0, 4, 0);//添加合并单元格,第一个参数是起始列,第二个参数是起始行,第三个参数是终止列,第四个参数是终止行
WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
Label title = new Label(0,0,"JExcelApi支持数据类型详细说明",titleFormate);
sheet.setRowView(0, 600, false);//设置第一行的高度
sheet.addCell(title);
//创建要显示的具体内容
WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
color.setColour(Colour.GOLD);//设置字体颜色为金黄色
WritableCellFormat colorFormat = new WritableCellFormat(color);
Label formate = new Label(0,1,"数据格式",colorFormat);
sheet.addCell(formate);
Label floats = new Label(1,1,"浮点型");
sheet.addCell(floats);
Label integers = new Label(2,1,"整型");
sheet.addCell(integers);
Label booleans = new Label(3,1,"布尔型");
sheet.addCell(booleans);
Label dates = new Label(4,1,"日期格式");
sheet.addCell(dates);
Label example = new Label(0,2,"数据示例",colorFormat);
sheet.addCell(example);
//浮点数据
//设置下划线
WritableFont underline= new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.NO_BOLD,false,UnderlineStyle.SINGLE);
WritableCellFormat greyBackground = new WritableCellFormat(underline);
greyBackground.setBackground(Colour.GRAY_25);//设置背景颜色为灰色
Number number = new Number(1,2,3.1415926535,greyBackground);
sheet.addCell(number);
//整形数据
WritableFont boldNumber = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//黑体
WritableCellFormat boldNumberFormate = new WritableCellFormat(boldNumber);
Number ints = new Number(2,2,15042699,boldNumberFormate);
sheet.addCell(ints);
//布尔型数据
Boolean bools = new Boolean(3,2,true);
sheet.addCell(bools);
//日期型数据
//设置黑体和下划线
WritableFont boldDate = new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.BOLD,false,UnderlineStyle.SINGLE);
WritableCellFormat boldDateFormate = new WritableCellFormat(boldDate,DateFormats.FORMAT1);
Calendar c = Calendar.getInstance();
Date date = c.getTime();
DateTime dt = new DateTime(4,2,date,boldDateFormate);
sheet.addCell(dt);
//把创建的内容写入到输出流中,并关闭输出流
workbook.write();
workbook.close();
os.close();
}
Java 实现Excel表数据的读取和写入 以及过程中可能遇到的问题
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。