返回目录:excel表格制作
java 读excel 还是比较方便简单的,原理就是,先用java 读取excel,然后,一行行的写入数据库,字段的话,你自己程序里面写就行了,给你个例子:
从Excel读取数据,生成新的Excel,以及修改Excel
package common.util;
import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;
import java.io.*;
/**
* Created by IntelliJ IDEA.
* User: xl
* Date: 2005-7-17
* Time: 9:33:22
* To change this template use File | Settings | File Templates.
*/
public class ExcelHandle
{
public ExcelHandle()
{
}
/**
* 读取Excel
*
* @param filePath
*/
public static void readExcel(String filePath)
{
try
{
InputStream is = new FileInputStream(filePath);
Workbook rwb = Workbook.getWorkbook(is);
//Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字,而为下标,从0开始
Sheet st = rwb.getSheet("original");
Cell c00 = st.getCell(0,0);
//通用的获取cell值的方式,返回字符串
String strc00 = c00.getContents();
//获得cell具体类型值的方式
if(c00.getType() == CellType.LABEL)
{
LabelCell labelc00 = (LabelCell)c00;
strc00 = labelc00.getString();
}
//输出
System.out.println(strc00);
//关闭e799bee5baa6e4b893e5b19e331
rwb.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
/**
* 输出Excel
*
* @param os
*/
public static void writeExcel(OutputStream os)
{
try
{
/**
* 只能通过API提供的工厂方法来创建Workbook,而不能使用WritableWorkbook的构造函数,
* 因为类WritableWorkbook的构造函数为protected类型
* method(1)直接从目标文件中读取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
* method(2)如下实例所示 将WritableWorkbook直接写入到输出流
*/
WritableWorkbook wwb = Workbook.createWorkbook(os);
//创建Excel工作表 指定名称和位置
WritableSheet ws = wwb.createSheet("Test Sheet 1",0);
//**************往工作表中添加数据*****************
//1.添加Label对象
Label label = new Label(0,0,"this is a label test");
ws.addCell(label);
//添加带有字型Formatting对象
WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
WritableCellFormat wcf = new WritableCellFormat(wf);
Label labelcf = new Label(1,0,"this is a label test",wcf);
ws.addCell(labelcf);
//添加带有字体颜色的Formatting对象
WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
Label labelCF = new Label(1,0,"This is a Label Cell",wcfFC);
ws.addCell(labelCF);
//2.添加Number对象
Number labelN = new Number(0,1,3.1415926);
ws.addCell(labelN);
//添加带有formatting的Number对象
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfN = new WritableCellFormat(nf);
Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN);
ws.addCell(labelNF);
//3.添加Boolean对象
Boolean labelB = new jxl.write.Boolean(0,2,false);
ws.addCell(labelB);
//4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date());
ws.addCell(labelDT);
//添加带有formatting的DateFormat对象
DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
WritableCellFormat wcfDF = new WritableCellFormat(df);
DateTime labelDTF = new DateTime(1,3,new java.util.Date(),wcfDF);
ws.addCell(labelDTF);
//添加图片对象,jxl只支持png格式图片
File image = new File("f:\\2.png");
WritableImage wimage = new WritableImage(0,1,2,2,image);
ws.addImage(wimage);
//写入工作表
wwb.write();
wwb.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
/**
* 拷贝后,进行修改,其中file1为被copy对象,file2为修改后创建的对象
* 尽单元格原有的格式化修饰是不能去掉的,我们还是可以将新的单元格修饰加上去,
* 以使单元格的内容以不同的形式表现
* @param file1
* @param file2
*/
public static void modifyExcel(File file1,File file2)
{
try
{
Workbook rwb = Workbook.getWorkbook(file1);
WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb);//copy
WritableSheet ws = wwb.getSheet(0);
WritableCell wc = ws.getWritableCell(0,0);
//判断单元格的类型,做出相应的转换
if(wc.getType == CellType.LABEL)
{
Label label = (Label)wc;
label.setString("The value has been modified");
}
wwb.write();
wwb.close();
rwb.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
实现代码如下:
import org.apache.poi.hssf.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
publicclass CreateCells
{
publicstaticvoid main(String[] args)
throws IOException
{
HSSFWorkbook wb = new HSSFWorkbook();//建立e69da5e6ba90e799bee5baa6e79fa5e98193361新HSSFWorkbook对象
HSSFSheet sheet = wb.createSheet("new sheet");//建立新的sheet对象
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short)0);//建立新行
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short)0);//建立新cell
cell.setCellValue(1);//设置cell的整数类型的值
// Or do it on one line.
row.createCell((short)1).setCellValue(1.2);//设置cell浮点类型的值
row.createCell((short)2).setCellValue("test");//设置cell字符类型的值
row.createCell((short)3).setCellValue(true);//设置cell布尔类型的值
HSSFCellStyle cellStyle = wb.createCellStyle();//建立新的cell样式
cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy h:mm"));//设置cell样式为定制的日期格式
HSSFCell dCell =row.createCell((short)4);
dCell.setCellValue(new Date());//设置cell为日期类型的值
dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式
HSSFCell csCell =row.createCell((short)5);
csCell.setEncoding(HSSFCell.ENCODING_UTF_16);//设置cell编码解决中文高位字节截断
csCell.setCellValue("中文测试_Chinese Words Test");//设置中西文结合字符串
row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);//建立错误cell
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}
java本身要生成excel文件必然是在后台做的复,通过poi库生成excel文件并制作表格。
无法直接通过网页保存生成excel。
至于下载到本地任意位置,也是后台生成了excel文件发送到前台(浏览器),由用户选择要存在哪儿,不能直接存储(这是web沙箱限制,不允许网页直接访问本地硬盘,不然你想想,如果你打开一个网页,网页代码可以任意访问你的硬盘,你还敢开网页吗)。
要绕过沙制箱限制必须装插件,也就是,你必须开发一个com或plugin插件,可zhidao以访问本地硬盘,但这需要用户手工安装(比如flash的插件,你之所以能用网页看flash是因为装了它的插件,但这是你手工装的,它不能绕过你直接给你装,它必须询问你行不行,你要手工点了OK,才能装)
如果是JSP页面要导出成excel,依靠javascript就可以实现7a686964616fe4b893e5b19e337了,具体你试试上面的代码
<input type="button" value="保存为 Excel">
<script language="javascript">
function AllAreaExcel() {
var title;
title=document.getElementsByTagName("table")[0].childNodes.item(0).childNodes(0).childNodes(0).innerText;
alert(title);
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
//从excel的第5行开始插入
oSheet.Range("A5").select;
oWB .Worksheets(1).Activate;
oSheet.Cells(3,1).Value=title; //在第3行插入报表头
oWB.Worksheets(1).Range("A3:I3").merge(); // 合并单元格区域 A3:I3
oWB.Worksheets(1).Range("A3:I3").HorizontalAlignment=3; //居中对齐A3:I3
var sel=document.body.createTextRange();
sel.moveToElementText(table1); //table 的ID值
sel.select();
sel.execCommand("Copy");
oSheet.Paste();
oXL.Visible = true;
}
</script>