读书人
栏目▼

java 操作excel,该怎么解决

发布时间： 2012-01-21 21:31:43 作者： rapoo

java 操作excel
关于excel怎么操作比较方面，我这里要实现一个导入功能，会把我的数据导给用户。主要是excel模板（我是swing实现的，所以希望提供java代码查考）。这个模板主要就是合并比较麻烦，另外里面的有复选框等等这些，还有一个关键是中间有些内容他的行数是不固定的，如片参考：http://zhidao.baidu.com/question/319120573.html


[解决办法]
有点乱 你自己整理下
封装了一个写数据的
一个调用格式撒的 原理就是基于模板修改数据

//copy附件
FileUtil.copyFile(from, to);
//准备修改
FileInputStream fis=new FileInputStream(to);
POIFSFileSystem fs = new POIFSFileSystem(fis);
HSSFWorkbook wb = new HSSFWorkbook(fs);

写数据的类



import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFAnchor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;




public class AffixExcel {
/*
* 写一行分离出来
*/
public void setRegionRow(HSSFSheet sheet,int row,List<KpiRegionValBean> urkl,String kpiname,HSSFCellStyle cs,HSSFCellStyle cs1){
if (row == 2) {
HSSFRow row1 = sheet.getRow(1);
if(row1==null)
row1 = sheet.createRow(1);
for (int ni = 0; ni < urkl.size(); ni++) {
HSSFCell c1 = row1.getCell(ni + 1);
if (c1 == null)
c1 = row1.createCell(ni + 1);
c1.setCellStyle(cs1);
c1.setCellType(HSSFCell.CELL_TYPE_STRING);
c1.setCellValue(urkl.get(ni).getRegion_name());
}
}

HSSFRow row2 = sheet.createRow(row);
if(row2==null)
row2 = sheet.createRow(row);
HSSFCell c = row2.getCell(0);
if (c == null)
c = row2.createCell(0);
c.setCellStyle(cs1);
c.setCellType(HSSFCell.CELL_TYPE_STRING);
c.setCellValue(kpiname);
for (int mi = 0; mi < urkl.size(); mi++) {
c = row2.getCell(mi + 1);
if (c == null)
c = row2.createCell(mi + 1);
c.setCellStyle(cs);
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(Double.valueOf(urkl.get(mi).getVal()));
}
}
/*
* 历史数据写入
*/
public void setDateRow(HSSFSheet sheet,int rowi,List<kpiDateValBean> urkd,String kpiname,HSSFCellStyle cs,HSSFCellStyle cs1){
if (rowi == 2) {
HSSFRow row = sheet.getRow(1);
if(row==null)
row = sheet.createRow(1);
for (int ni = 0; ni < urkd.size(); ni++) {
HSSFCell c = row.getCell(ni + 1);
if (c == null)
c = row.createCell(ni + 1);
c.setCellStyle(cs1);
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(urkd.get(ni).getDate_key()
.substring(
urkd.get(ni).getDate_key()
.length() - 2));
}
}

HSSFRow row2 = sheet.createRow(rowi);
if(row2==null)
row2 = sheet.createRow(rowi);
HSSFCell c1 = row2.getCell(0);
if (c1 == null)
c1 = row2.createCell(0);
c1.setCellStyle(cs1);
c1.setCellType(HSSFCell.CELL_TYPE_STRING);
c1.setCellValue(kpiname);
for (int mi = 0; mi < urkd.size(); mi++) {
c1 = row2.getCell(mi + 1);
if (c1 == null)
c1 = row2.createCell(mi + 1);
c1.setCellStyle(cs);
c1.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c1.setCellValue(Double.valueOf(urkd.get(mi).getVal()));
}
}

/*
* 写第一行的时间
*/
public void setTime(HSSFSheet sheet,String time,HSSFCellStyle cs){
HSSFRow row = sheet.getRow(0);
if(row==null)
row = sheet.createRow(0);
HSSFCell cell = row.getCell(0);
if (cell == null)
cell = row.createCell(0);
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(time);
}

/*
* 写第一行的名称


*/
public void setTitle(HSSFSheet sheet,String Title,HSSFCellStyle cs){
HSSFRow row = sheet.getRow(0);
if(row==null)
row = sheet.createRow(0);
HSSFCell cell = row.getCell(0);
if (cell == null)
cell = row.createCell(0);
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(Title);
}

}

使用的lib poi.jar 别的你自己找找吧
[解决办法]
代码如下，可供参考

package cn.ipanel.itime.util;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiExcel2 {
public static List<String[]> read(String file, boolean flag) throws Exception {
Workbook wb = null;
File f = new File(file);
FileInputStream is = new FileInputStream(f);
if (flag) {// 2003
wb = new HSSFWorkbook(is);
} else {// 2007
wb = new XSSFWorkbook(is);
}
return read(wb);
}

public static List<String[]> read(Workbook wb) throws Exception {
List<String[]> list =new ArrayList<String[]>();
try {
for (int k = 0; k < wb.getNumberOfSheets(); k++) { //页
// sheet
Sheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) { //行
String str = new String();
// 定义 row
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();

for (short c = 0; c < cells; c++) { //列
Cell cell = row.getCell(c);
if (cell != null) {
String value = null;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;

case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value =cell.getDateCellValue().toString();

} else {
int number = (int) cell.getNumericCellValue();
value = number + "";
}
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
cell.getDateCellValue();

break;
default:

}
System.out.println(value);
// 第三行才开始添加数据
if (r > 1 || r == 1) {
//如果是最后一列，不加“ ，”
if(c!=cells){
str += value + ",";
}else{
str += value;
}
}
} //判断cell是否为空结束
}//循环列结束
//将数组存入List集合中,从第二列开始加到数组
if(r>1 || r==1){
String[] strCell = str.split(",");
list.add(strCell);
}
} //判断行是否为空结束

}
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}

public static void main(String[] args) throws Exception {
List<String[]> list=read("f:\\历史上的今天-资料.xlsx", false);
System.out.println("list大小:"+list.size());
/*for(int i=0;i<list.size();i++){
String[] c=list.get(i);


for(int k=0;k<c.length;k++){
System.out.println(c[k]);
}
}*/
}

}
使用的是poi读取Excel数据到数据
[解决办法]
给你个实际操作的，自己可以提取共性封装成类
String excelPath = null;
// 创建流
try {
FormFile file = tblStudent.getFile();

String fileName = file.getFileName();

String size = Integer.toString(file.getFileSize()) + "bytes";

java.io.InputStream is = file.getInputStream();
String path = servlet.getServletContext().getRealPath("/upload");

// 创建文件,如果不传在创建所有层次的目录
File dirFile = new File(path);
if (!dirFile.exists()) {
dirFile.mkdirs();
}
excelPath = path + "/" + fileName;
OutputStream os = new FileOutputStream(excelPath);

int bytes = 0;

byte[] buffer = new byte[8192];

while ((bytes = is.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytes);
}
// 关闭流
os.close();

is.close();

file.destroy();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ArrayList<TblStudent> list = new ArrayList<TblStudent>();
TblStudent tblStudent1 = null;
String c = request.getParameter("");
// 创建单元格对象
Cell cell = null;
// 获取excel文件
Workbook book;
// 判断添加的对象是否和班级人数匹配
int cellRows = 0;

// 异常信息
String msg = null;
try {
book = Workbook.getWorkbook(new File(excelPath));
// 获取 第一个 工作表对象
Sheet sheet = book.getSheet(0);
// 循环行
TblStudent tblStu = null;
TblGrade tblGrade = null;
TblClass tblClass = null;
TblTeacher tblTeacher = null;
String stuName = null;
String className = null;
String gradeName = null;

for (int i = 1; i < sheet.getRows(); i++) {
tblStudent1 = new TblStudent();

cellRows = sheet.getRows() - 1;
for (int j = 0; j < sheet.getColumns(); j++) {
cell = sheet.getCell(j, i);// 获取第i+1行第j+1列的单元格

switch (j) {
case 0:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
tblStudent1.setStuName(cell.getContents().trim());
} else {
msg = "学生姓名不能为空!";
break;
}
break;
case 1:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
// 根据年级名查年纪ID
try {
tblGrade = studentBiz.findGradeByName(cell
.getContents().trim());
tblStudent1.getTblGrade().setGradeId(
tblGrade.getGradeId());
} catch (Exception e) {
// TODO Auto-generated catch block
msg = "年级名无效!";
}
} else {
msg = "年纪不能为空!";
break;
}
break;
case 2:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
try {
tblClass = studentBiz.findClassByName(cell
.getContents().trim());
tblStudent1.getTblClass().setClassId(
tblClass.getClassId());
} catch (Exception e) {
// TODO Auto-generated catch block
msg = "班级名无效!";
}
} else {
msg = "班级名不能为空";
break;
}
break;
case 3:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
try {
tblTeacher = studentBiz.findTeacherByTName(cell


.getContents().trim());
tblStudent1.getTblTeacher().setTId(
tblTeacher.getTId());
} catch (Exception e) {
// TODO Auto-generated catch block
msg = "没有此教师,请重新填写!";
}
} else {
msg = "教师名不能为空";
break;
}
break;
case 4:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
try {
Integer age = new Integer(cell.getContents());
if (age < 0 || age > 100) {
msg = "学生年龄只能大于0小于100!";
} else {
tblStudent1.setStuAge(new Integer(cell
.getContents().trim()));
}
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
msg = "学生年龄只能为整数";
}
} else {
msg = "学生年龄不能为空!";
break;
}
break;
case 5:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
try {
tblStudent1
.setStuTel(cell.getContents().trim());
} catch (Exception e) {
// TODO Auto-generated catch block
msg = "电话号码不能为空!";
}
} else {
msg = "电话好嘛不能为空!";
break;
}
break;
case 6:
if (cell.getContents() != null
&& !cell.getContents().equals("")) {
tblStudent1
.setStuAddress(cell.getContents().trim());
} else {
msg = "学生家庭住址不能为空!";
break;
}
break;
}

}
list.add(tblStudent1);
}
book.close();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 向数据库中添加数据
for (int i = 0; i < list.size(); i++) {
TblStudent student = (TblStudent) list.get(i);
// 学生个人信息密码
String pass = "123";
Encoder encoder = new Encoder();
try {

tblStudent1.setStuPass(encoder.EncoderByMd5(pass));
} catch (NoSuchAlgorithmException e) {
// TODO Auto-generated catch block
msg = "输入的密码格式不正确!";
}
try {
// 根据班级人数判断excel表格中的行数是否与班级人数匹配
if (student != null) {
studentBiz.addStudent(student);
response.getWriter().println(
"<script language='javascript'>");
response
.getWriter()
.println(
"alert('"
+ cellRows
+ "名学生信息添加成功！');location.href='/AnaSys/index.jsp';");
response.getWriter().println("</script>");
response.getWriter().flush();
}

} catch (Exception e) {
// TODO Auto-generated catch block
response.getWriter().println("<script language='javascript'>");
response
.getWriter()
.println(
"alert('"
+ msg
+ ",添加失败！');location.href='/AnaSys/index.jsp';");
response.getWriter().println("</script>");
response.getWriter().flush();
}
}
response.getWriter().println("<script language='javascript'>");
response
.getWriter()
.println(
"alert('" + msg
+ ",添加失败！');location.href='/AnaSys/index.jsp';");
response.getWriter().println("</script>");
response.getWriter().flush();
return null;
}
------解决方案--------------------


Java code
合并方法            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));            sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 4));            sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 18));            sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 34));
读书人网 >J2EE开发

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net