用java写网络版进销存数据如何导入导出成excel文件?

zhone 2010-04-04 05:29:46
如题,能给导入导出表成excel的例子最好。发我邮箱也行—— zhone91@126.com (问题解决后马上给分)
...全文
268 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhone 2010-04-07
  • 打赏
  • 举报
回复
谢谢各位前辈的关注。
我花了2天时间学习了JXL,问题解决了。
有需要的朋友们可以联系我,我写出了完整的jxl操作excel出入数据库的简单例子。
结贴咯,辛苦大家了。呵呵。
zhangzhen6960 2010-04-06
  • 打赏
  • 举报
回复
学习一下
xuexijava 2010-04-05
  • 打赏
  • 举报
回复
学习。。。。。。。。。。。。
zhone 2010-04-05
  • 打赏
  • 举报
回复
在网上查到可以用POI和JXL实现。
谁能给个用JXL实现操作EXCEL出入数据库的例子么?
zhone 2010-04-05
  • 打赏
  • 举报
回复
谢谢各位的关注。
请问谁有JXL读写excel出入数据库的完整例子啊?!(传到我邮箱也可以。)
smlszd 2010-04-05
  • 打赏
  • 举报
回复
/*---------------以下为创建excel文件----------------*/
package test;

// 生成Excel的类
import java.io.File;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class CreateExcel {
public static void main(String args[]) {
try {
// 打开文件
WritableWorkbook book = Workbook.createWorkbook( new File( " test.xls " ));
// 生成名为“第一页”的工作表,参数0表示这是第一页
WritableSheet sheet = book.createSheet( " 第一页 " , 0 );
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// 以及单元格内容为test
Label label = new Label( 0 , 0 , " test " );

// 将定义好的单元格添加到工作表中
sheet.addCell(label);

/**/ /*
* 生成一个保存数字的单元格 必须使用Number的完整包路径,否则有语法歧义 单元格位置是第二列,第一行,值为789.123
*/
jxl.write.Number number = new jxl.write.Number( 1 , 0 , 555.12541 );
sheet.addCell(number);

// 写入数据并关闭文件
book.write();
book.close();

} catch (Exception e) {
System.out.println(e);
}
}
}

/*-----------------以下为读excel文件----------------*/
package test;

// 读取Excel的类
import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ReadExcel {
public static void main(String args[]) {
try {
Workbook book = Workbook.getWorkbook( new File( " test.xls " ));
// 获得第一个工作表对象
Sheet sheet = book.getSheet( 0 );
// 得到第一列第一行的单元格
Cell cell1 = sheet.getCell( 0 , 0 );
String result = cell1.getContents();
System.out.println(result);
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
}

/*---------------package test;

// 读取Excel的类
import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ReadExcel {
public static void main(String args[]) {
try {
Workbook book = Workbook.getWorkbook( new File( " test.xls " ));
// 获得第一个工作表对象
Sheet sheet = book.getSheet( 0 );
// 得到第一列第一行的单元格
Cell cell1 = sheet.getCell( 0 , 0 );
String result = cell1.getContents();
System.out.println(result);
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
/*----------以下是修改excel文件--------*/
package test;

import java.io.File;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class UpdateExcel {
public static void main(String args[]) {
try {
// Excel获得文件
Workbook wb = Workbook.getWorkbook( new File( " test.xls " ));
// 打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook( new File( " test.xls " ),
wb);
// 添加一个工作表
WritableSheet sheet = book.createSheet( " 第二页 " , 1 );
sheet.addCell( new Label( 0 , 0 , " 第二页的测试数据 " ));
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
  • 打赏
  • 举报
回复
这个需要一个包:专门把DB中数据导入EXCEL,下面是我以前做过的一个项目代码你卡看
package emanagement;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.sql.*;
import javax.swing.JFileChooser;
import java.io.FileOutputStream;
import java.io.*;
import javax.swing.JOptionPane;
import java.util.Vector;

public class ExportExcel {
ResultSet rs;
public ExportExcel() {
}
public void export() throws IOException {
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet=null;
int k=0;
ConnectionDB cdb=new ConnectionDB();
rs=cdb.ExportExcel();
if(k==0)
{
sheet=workbook.createSheet("入库数据");
setInfantCellTitle(sheet);
k++;
}
try {
while (rs.next()) {
HSSFRow row=sheet.createRow(k);
row.createCell((short)0).setCellValue(rs.getString(2));
row.createCell((short)1).setCellValue(rs.getString(3));
row.createCell((short)2).setCellValue(rs.getString(4));
row.createCell((short)3).setCellValue(rs.getString(5));
row.createCell((short)4).setCellValue(rs.getString(6));
row.createCell((short)5).setCellValue(rs.getString(7));
row.createCell((short)6).setCellValue(rs.getString(8));
row.createCell((short)7).setCellValue(rs.getString(9));
row.createCell((short)8).setCellValue(rs.getString(10));
row.createCell((short)9).setCellValue(rs.getString(11));
k++;
}
try {
JFileChooser jfile=new JFileChooser();
jfile.showOpenDialog(null);
String file=jfile.getSelectedFile().getPath();
FileOutputStream fOut = null;
try {
fOut = new FileOutputStream(file);
}
catch (FileNotFoundException ex2) {
}
workbook.write(fOut);
fOut.flush();
fOut.close();
JOptionPane.showMessageDialog(null, "数据导出成功!","数据导出",2);
}
catch (FileNotFoundException ex2) {
ex2.printStackTrace();
}

}
catch (SQLException ex) {
ex.printStackTrace();
}finally
{
try {
ConnectionDB.con.close();
rs.close();
}
catch (SQLException ex1) {
ex1.printStackTrace();
}
}

}
public void setInfantCellTitle(HSSFSheet sheet)
{
HSSFRow row=sheet.createRow(0);
row.createCell((short)0).setCellValue("类型");
row.createCell((short)1).setCellValue("类别");
row.createCell((short)2).setCellValue("名称");
row.createCell((short)3).setCellValue("规格/型号");
row.createCell((short)4).setCellValue("单位");
row.createCell((short)5).setCellValue("数量");
row.createCell((short)6).setCellValue("入库人");
row.createCell((short)7).setCellValue("入库时间");
row.createCell((short)8).setCellValue("存放位置");
row.createCell((short)9).setCellValue("备注");
}
public void InExport(Vector vec)throws IOException //入库统计数据导出
{
Vector vec1=new Vector();
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet=null;
int k=0;
if(k==0)
{
sheet=workbook.createSheet("入库统计数据");
setInfantCellTitle1(sheet);
k++;
}
int rows=vec.size();
for(int i=0;i<rows;i++)
{
HSSFRow row=sheet.createRow(k);
vec1=(Vector)vec.elementAt(i);
for (int j = 0; j < 7; j++)
{
row.createCell((short)j).setCellValue(vec1.elementAt(j).toString());
}
k++;
}
try {
JFileChooser jfile=new JFileChooser();
jfile.showOpenDialog(null);
String file=jfile.getSelectedFile().getPath();
FileOutputStream fOut = null;
try {
fOut = new FileOutputStream(file);
}
catch (FileNotFoundException ex2) {
}
workbook.write(fOut);
fOut.flush();
fOut.close();
JOptionPane.showMessageDialog(null, "数据导出成功!","数据导出",2);
}
catch (FileNotFoundException ex2) {
ex2.printStackTrace();
}

}
public void setInfantCellTitle1(HSSFSheet sheet)
{
HSSFRow row=sheet.createRow(0);
row.createCell((short)0).setCellValue("类型");
row.createCell((short)1).setCellValue("类别");
row.createCell((short)2).setCellValue("名称");
row.createCell((short)3).setCellValue("规格/型号");
row.createCell((short)4).setCellValue("数量");
row.createCell((short)5).setCellValue("入库时间");
row.createCell((short)6).setCellValue("存放位置");
}
public void OutExport(Vector vec)throws IOException //入出统计数据导出
{
Vector vec1=new Vector();
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet=null;
int k=0;
if(k==0)
{
sheet=workbook.createSheet("出库统计数据");
setInfantCellTitle2(sheet);
k++;
}
int rows=vec.size();
for(int i=0;i<rows;i++)
{
HSSFRow row=sheet.createRow(k);
vec1=(Vector)vec.elementAt(i);
for (int j = 0; j <8; j++)
{
row.createCell((short)j).setCellValue(vec1.elementAt(j).toString());
}
k++;
}
try {
JFileChooser jfile=new JFileChooser();
jfile.showOpenDialog(null);
String file=jfile.getSelectedFile().getPath();
FileOutputStream fOut = null;
try {
fOut = new FileOutputStream(file);
}
catch (FileNotFoundException ex2) {
}
workbook.write(fOut);
fOut.flush();
fOut.close();
JOptionPane.showMessageDialog(null, "数据导出成功!","数据导出",2);
}
catch (FileNotFoundException ex2) {
ex2.printStackTrace();
}

}
public void setInfantCellTitle2(HSSFSheet sheet)
{
HSSFRow row=sheet.createRow(0);
row.createCell((short)0).setCellValue("类型");
row.createCell((short)1).setCellValue("类别");
row.createCell((short)2).setCellValue("名称");
row.createCell((short)3).setCellValue("规格/型号");
row.createCell((short)4).setCellValue("数量");
row.createCell((short)5).setCellValue("领用人");
row.createCell((short)6).setCellValue("出库时间");
row.createCell((short)7).setCellValue("用途");
}
public void CurrentExport(Vector vec)throws IOException //现存量统计数据导出
{
Vector vec1=new Vector();
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet=null;
int k=0;
if(k==0)
{
sheet=workbook.createSheet("现存量数据");
setInfantCellTitle3(sheet);
k++;
}
int rows=vec.size();
for(int i=0;i<rows;i++)
{
HSSFRow row=sheet.createRow(k);
vec1=(Vector)vec.elementAt(i);
for (int j = 0; j <10; j++)
{
row.createCell((short)j).setCellValue(vec1.elementAt(j).toString());
}
k++;
}
try {
JFileChooser jfile=new JFileChooser();
jfile.showOpenDialog(null);
String file=jfile.getSelectedFile().getPath();
FileOutputStream fOut = null;
try {
fOut = new FileOutputStream(file);
}
catch (FileNotFoundException ex2) {
}
workbook.write(fOut);
fOut.flush();
fOut.close();
JOptionPane.showMessageDialog(null, "数据导出成功!","数据导出",2);
}
catch (FileNotFoundException ex2) {
ex2.printStackTrace();
}

}
public void setInfantCellTitle3(HSSFSheet sheet)
{
HSSFRow row=sheet.createRow(0);
row.createCell((short)0).setCellValue("类型");
row.createCell((short)1).setCellValue("类别");
row.createCell((short)2).setCellValue("名称");
row.createCell((short)3).setCellValue("规格/型号");
row.createCell((short)4).setCellValue("单位");
row.createCell((short)5).setCellValue("数量");
row.createCell((short)6).setCellValue("入库人");
row.createCell((short)7).setCellValue("入库时间");
row.createCell((short)8).setCellValue("位置");
row.createCell((short)9).setCellValue("备注");
}

}
kaida_7 2010-04-04
  • 打赏
  • 举报
回复
看一下这哥们儿的代码,也许你就能做了http://yongsky.javaeye.com/blog/99075
kaida_7 2010-04-04
  • 打赏
  • 举报
回复
这个是excel导出的代码
kaida_7 2010-04-04
  • 打赏
  • 举报
回复
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@include file="header.jsp" %>
<%!
public void cellcreate(HSSFRow ss, int index, String info) {
HSSFCell cell = ss.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(info);
}
%>
<%
String name = (String)request.getAttribute("name");
List list = (List)request.getAttribute("list");
List daoname = null;
List ln = null;
List lns = null;
//System.out.println(list.size());
Student s = null;
String message = "";
String tname = "";

String ExcelPath = application.getRealPath("")+"\\excel\\"+session.getId()+".xls";
String URI = "../excel/";
new File(ExcelPath).delete();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
try{
int index = 1;
HSSFRow row = sheet.createRow(0);
List colName = new ArrayList();
colName.add("教师姓名");
colName.add("所在分院");
colName.add("专业");
colName.add("年级");
colName.add("班级");
colName.add("应参评人数");
colName.add("已参评人数");
colName.add("参评情况");
colName.add("得分");

for(int i=0 ; i<colName.size() ; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue((String)colName.get(i));
}
%>
<p>导师测评结果</p>
<p><a href="../excel/<%=session.getId() %>.xls">下载该文件</a></p>
<table id="tab" border="1">
<tr>
<td width="30">序号</td>
<td width="50">教师姓名</td>
<td width="60">所在分院</td>
<td width="60">专业</td>
<td width="30">年级</td>
<td width="60">班级</td>
<td width="40">应参评人数</td>
<td width="40">已参评人数</td>
<td width="80">参评情况</td>
<td width="50">得分</td>
</tr>
<%
for(int i=0; i<list.size(); i++){
HSSFRow ss = sheet.createRow(index++);
int a = 0;
int b = 0;

s = (Student)list.get(i);
String did = s.getDid();
String sclass = s.getSclass();
String grade = s.getGrade();
String major = s.getMajor();
String sql = "";

String params [] = {did,sclass,major,grade};
QueryRunner qr = DBHelper.getQueryRunner();

sql = "select * from teacher where id=?";
String paramdsn [] = {did};
try {
daoname = (List) qr.query(sql, paramdsn, new BeanListHandler(
Teacher.class));
} catch (SQLException e) {
e.printStackTrace();
System.out.println("找导师时出错!");
}
if(daoname.size() == 0){
message = "";
out.println(message);
}else{
Teacher t = (Teacher)daoname.get(0);
tname = t.getName();

%>
<tr>
<td><%=(i+1) %></td>
<td>
<%=tname %>
<%cellcreate(ss, 0, tname);
%>
</td>
<td><%=s.getCompart() %>
<%cellcreate(ss, 1, s.getCompart());%>
</td>
<td><%=s.getMajor() %><%cellcreate(ss, 2, s.getMajor());%></td>
<td><%=s.getGrade() %><%cellcreate(ss, 3, s.getGrade());%></td>
<td><%=s.getSclass() %><%cellcreate(ss, 4, s.getSclass());%></td>
<td><%//应参评人数
sql = "select * from student where did=? and sclass=? and major=? and grade=?";
try {
ln = (List)qr.query(sql, params, new BeanListHandler(Student.class));
} catch (SQLException e) {
e.printStackTrace();
System.out.println("找导师时出错!");
}if(ln.size() == 0){
message = "没找到你们班的学生,请与管理员联系,谢谢!";
}else{
a = ln.size();
//System.out.println(a);
out.println(a);
}
cellcreate(ss, 5, a+"");
%>
</td>

<td>
<%//已经参评人数
sql = "select * from student where did=? and sclass=? and major=? and grade=? and dscore>0";
try {
lns = (List)qr.query(sql, params, new BeanListHandler(Student.class));
} catch (SQLException e) {
e.printStackTrace();
System.out.println("找导师时出错!");
}if(lns.size() == 0){
message = "还没有学生参加测评";
cellcreate(ss, 6, message);
out.println(message);
}else{
b = lns.size();
out.println(b);
cellcreate(ss, 6, b+"");
}

%>
</td>
<td><%
if(a>b){
message = "还有"+(a-b)+"个同学没有进行测评!";
out.println(message);
cellcreate(ss, 7, message);
}else{
String msg = "测评完毕!";
out.println(msg);
cellcreate(ss, 7, msg);
}
%></td>
<td><%=s.getAvgdscore() %><%cellcreate(ss, 8, ""+s.getAvgdscore());%></td>
</tr>
<% }
}%>
</table>
<table width="763" border="0" align="center" >
<tr>
<td height="63" colspan="5" bgcolor="#023880" class="h">地址:中国·辽宁·大连市经济技术开发区铁山西路31号 电话:0411-82171568 传真:0411-82171569 E-mail:<br/>ut.edu.cn
版权所有:大连理工大学</td>
</tr>
</table>
</div>
</div>
</body>
</html>
<%
FileOutputStream fos = new FileOutputStream(ExcelPath);
workbook.write(fos);
fos.flush();
fos.close();
} catch(Exception e){
e.printStackTrace();
System.out.println("统计时生成Excel表格出错!");
}

%>

xiaohuanjie 2010-04-04
  • 打赏
  • 举报
回复

可以使用第三方工具,例如MySQL 可以用Navicat for MySQL

SQL SERVER 自身就带有这样的工具
zhone 2010-04-04
  • 打赏
  • 举报
回复
前辈们出来给点提示呀!
zhone 2010-04-04
  • 打赏
  • 举报
回复
要excel能通过java程序导入数据库,也要能从数据库导出生成到excel中。
那位前辈能给我个例子,越完整越好!

81,122

社区成员

发帖
与我相关
我的任务
社区描述
Java Web 开发
社区管理员
  • Web 开发社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧