引用：
*mysql的jar文盯李件
*Spring_HOME/lib/poi/燃判*.jar
2，编写数据库链接类
package com.zzg.db
import java.sql.Connection
import java.sql.DriverManager
public class DbUtils {
private static Connection conn
static {
try {
Class.forName("com.mysql.jdbc.Driver")
conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456")
} catch (Exception e) {
e.printStackTrace()
}
}
public static Connection getConn() {
return conn
}
public static void setConn(Connection conn) {
DbUtils.conn = conn
}
}
3，编写数据库 *** 作类
package com.zzg.db
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.SQLException
public class ExcuteData {
private PreparedStatement pstmt
public boolean ExcuData(String sql) {
Connection conn = DbUtils.getConn()
boolean flag=false
try {
pstmt = conn.prepareStatement(sql)
flag=pstmt.execute()
} catch (SQLException e) {
e.printStackTrace()
}
return flag
}
}
4，编写的Excel表格实体类
package com.zzg.model
public class TableCell {
private String _name
private String _value
public String get_name() {
return _name
}
public void set_name(String _name) {
this._name = _name
}
public String get_value() {
return _value
}
public void set_value(String _value) {
this._value = _value
}
}
5，编写主键生成方法
package com.zzg.util
import java.text.SimpleDateFormat
import java.util.Date
import java.util.Random
public class GenericUtil {
public static String getPrimaryKey()
{
String primaryKey
primaryKey = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
Random r = new Random()
primaryKey +=r.nextInt(100000)+100000
return primaryKey
}
}
6，编写的Excel *** 作类
package com.zzg.deployData
import java.io.File
import java.io.FileInputStream
import java.io.FileNotFoundException
import java.io.IOException
import java.io.Serializable
import java.util.ArrayList
import java.util.List
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import com.zzg.db.ExcuteData
import com.zzg.model.TableCell
import com.zzg.util.GenericUtil
public class OperExcel<T extends Serializable>{
private HSSFWorkbook workbook
private String tableName
private Class<T>type
private String sheetName
public OperExcel(File excelFile, String tableName, Class<T>type,
String sheetName) throws FileNotFoundException,
IOException {
workbook = new HSSFWorkbook(new FileInputStream(excelFile))
this.tableName = tableName
this.type = type
this.sheetName = sheetName
InsertData()
}
// 向表中写入数据
public void InsertData() {
System.out.println("yyy")
ExcuteData excuteData = new ExcuteData()
List<List>datas = getDatasInSheet(this.sheetName)
// 向表中添加数据之前先删除表中数据
String strSql = "delete from " + this.tableName
excuteData.ExcuData(strSql)
// 拼接sql语句
for (int i = 1i <datas.size()i++) {
strSql = "insert into " + this.tableName + "("
List row = datas.get(i)
for (short n = 0n <row.size()n++) {
TableCell excel = (TableCell) row.get(n)
if (n != row.size() - 1)
strSql += excel.get_name() + ","
else
strSql += excel.get_name() + ")"
}
strSql += " values ("
for (short n = 0n <row.size()n++) {
TableCell excel = (TableCell) row.get(n)
try {
if (n != row.size() - 1) {
strSql += getTypeChangeValue(excel) + ","
} else
strSql += getTypeChangeValue(excel) + ")"
} catch (RuntimeException e) {
e.printStackTrace()
} catch (Exception e) {
e.printStackTrace()
}
}
//执行sql
excuteData.ExcuData(strSql)
}
}
/**
* 获得表中的数据
* @param sheetName 表格索引(EXCEL 是多表文档,所以需要输入表索引号)
* @return 由LIST构成的行和表
*/
public List<List>getDatasInSheet(String sheetName) {
List<List>result = new ArrayList<List>()
// 获得指定的表
HSSFSheet sheet = workbook.getSheet(sheetName)
// 获得数据总行数
int rowCount = sheet.getLastRowNum()
if (rowCount <1) {
return result
}
// 逐行读取数据
for (int rowIndex = 0rowIndex <rowCountrowIndex++) {
// 获得行对象
HSSFRow row = sheet.getRow(rowIndex)
if (row != null) {
List<TableCell>rowData = new ArrayList<TableCell>()
// 获得本行中单元格的个数
int columnCount = sheet.getRow(0).getLastCellNum()
// 获得本行中各单元格中的数据
for (short columnIndex = 0columnIndex <columnCountcolumnIndex++) {
HSSFCell cell = row.getCell(columnIndex)
// 获得指定单元格中数据
Object cellStr = this.getCellString(cell)
TableCell TableCell = new TableCell()
TableCell.set_name(getCellString(
sheet.getRow(0).getCell(columnIndex)).toString())
TableCell.set_value(cellStr == null ? "" : cellStr
.toString())
rowData.add(TableCell)
}
result.add(rowData)
}
}
return result
}
/**
* 获得单元格中的内容
* @param cell
* @return result
*/
protected Object getCellString(HSSFCell cell) {
Object result = null
if (cell != null) {
int cellType = cell.getCellType()
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue()
break
case HSSFCell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue()
break
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue()
break
case HSSFCell.CELL_TYPE_ERROR:
result = null
break
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue()
break
case HSSFCell.CELL_TYPE_BLANK:
result = null
break
}
}
return result
}
// 根据类型返回相应的值
@SuppressWarnings("unchecked")
public String getTypeChangeValue(TableCell excelElement)
throws RuntimeException, Exception {
String colName = excelElement.get_name()
String colValue = excelElement.get_value()
String retValue = ""
if (colName.equals("id")) {
retValue = "'" + GenericUtil.getPrimaryKey() + "'"
return retValue
}
if (colName == null) {
retValue = null
}
if (colName.equals("class_createuser")) {
retValue = "yaa101"
return "'" + retValue + "'"
}
retValue = "'" + colValue + "'"
return retValue
}
}
7，编写调用 *** 作的Excel类的方法
package com.zzg.deployData
import java.io.File
import java.io.FileNotFoundException
import java.io.IOException
public class DeployData {
private File fileOut
public void excute(String filepath) {
fileOut = new File(filepath)
this.deployUserInfoData()
}
public void deployUserInfoData() {
try {
new OperExcel(fileOut, "test", Object.class, "Sheet1")
} catch (FileNotFoundException e) {
e.printStackTrace()
} catch (IOException e) {
e.printStackTrace()
}
}
}
8，编写客户端
package com.zzg.client
import com.zzg.deployData.DeployData
public class DeployClient {
public static void main(String[] args) {
DeployData deployData = new DeployData()
deployData.excute("D://test.xls")
}
}
java查询出的内圆唤穗容导入到excel表格/**导出数据橘卜为XLS格式
* @param fos
* @param bo
*/
public void writeExcelBo(FileOutputStream fos, java.util.Vector ve)
{
jxl.write.WritableWorkbook wwb
try
{
wwb= Workbook.createWorkbook(fos)
jxl.write.WritableSheet ws= wwb.createSheet("booksheet", 10)
ws.addCell(new jxl.write.Label(0, 1, "书目ID"))
ws.addCell(new jxl.write.Label(1, 1, "ISBN"))
ws.addCell(new jxl.write.Label(2, 1, "定价"))
ws.addCell(new jxl.write.Label(3, 1, "书名"))
ws.addCell(new jxl.write.Label(4, 1, "原书名"))
ws.addCell(new jxl.write.Label(5, 1, "副题名"))
ws.addCell(new jxl.write.Label(6, 1, "著者"))
ws.addCell(new jxl.write.Label(7, 1, "译者"))
ws.addCell(new jxl.write.Label(8, 1, "版次"))
ws.addCell(new jxl.write.Label(9, 1, "出版地"))
ws.addCell(new jxl.write.Label(10, 1, "出版社"))
ws.addCell(new jxl.write.Label(11, 1, "出版日期"))
ws.addCell(new jxl.write.Label(12, 1, "页数"))
ws.addCell(new jxl.write.Label(13, 1, "书高"))
ws.addCell(new jxl.write.Label(14, 1, "装帧链拦"))
ws.addCell(new jxl.write.Label(15, 1, "丛书名"))
ws.addCell(new jxl.write.Label(16, 1, "一般性附注项"))
ws.addCell(new jxl.write.Label(17, 1, "简介"))
ws.addCell(new jxl.write.Label(18, 1, "主题词"))
ws.addCell(new jxl.write.Label(19, 1, "中图法分类"))
ws.addCell(new jxl.write.Label(20, 1, "更新日期"))
ws.addCell(new jxl.write.Label(21, 1, "本数"))
book=new Book[ve.size()]
for (int i= 0i <ve.size()i++)
{
book[i]= (Book)ve.get(i)
ws.addCell(new jxl.write.Label(0, i + 2, "" + book[i].getBookId()))
ws.addCell(new jxl.write.Label(1, i + 2, book[i].getIsbn()))
ws.addCell(new jxl.write.Label(2, i + 2, "" + book[i].getPrice()))
ws.addCell(new jxl.write.Label(3, i + 2, book[i].getBookTitle()))
ws.addCell(new jxl.write.Label(4, i + 2, book[i].getOldFilename()))
ws.addCell(new jxl.write.Label(5, i + 2, book[i].getSubTitle()))
ws.addCell(new jxl.write.Label(6, i + 2, book[i].getWriter()))
ws.addCell(new jxl.write.Label(7, i + 2, book[i].getTranscribe()))
ws.addCell(new jxl.write.Label(8, i + 2, "" + book[i].getVersion()))
ws.addCell(new jxl.write.Label(9, i + 2, book[i].getPublishCity()))
ws.addCell(new jxl.write.Label(10, i + 2, book[i].getPublisher()))
ws.addCell(new jxl.write.Label(11, i + 2, book[i].getPublishDate().toString()))
ws.addCell(new jxl.write.Label(12, i + 2, "" + book[i].getPage()))
ws.addCell(new jxl.write.Label(13, i + 2, "" + book[i].getHight()))
ws.addCell(new jxl.write.Label(14, i + 2, book[i].getInstall()))
ws.addCell(new jxl.write.Label(15, i + 2, book[i].getSeries()))
ws.addCell(new jxl.write.Label(16, i + 2, book[i].getNotes()))
ws.addCell(new jxl.write.Label(17, i + 2, book[i].getPrecisnotes()))
ws.addCell(new jxl.write.Label(18, i + 2, book[i].getSubject()))
ws.addCell(new jxl.write.Label(19, i + 2, book[i].getCls().replaceAll("_", "")))
ws.addCell(new jxl.write.Label(20, i + 2, book[i].getUpdatedate().toString()))
ws.addCell(new jxl.write.Label(21, i + 2, "0"))
}
jxl.write.WritableFont wfc=
new jxl.write.WritableFont(
WritableFont.ARIAL,
255,
WritableFont.BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK)
jxl.write.WritableCellFormat wcfFC= new jxl.write.WritableCellFormat(wfc)
ws.addCell(new jxl.write.Label(0, 0, "为保证您提交定单的稳定和正确，导入定单时候请勿更改此表格式（请勿更改书目ID,订购本数自行添加！）"))
wwb.write()
//关闭Excel工作薄对象
wwb.close()
} catch (IOException e)
{} catch (RowsExceededException e)
{} catch (WriteException e)
{}
}
//导入EXCEL
if (f.getName().indexOf(".xls") >0)
{
try
{
fis= new FileInputStream(f)
BookBean bob= new BookBean()
UserBean usb= new UserBean()
jxl.Workbook rwb= Workbook.getWorkbook(fis)
jxl.Sheet sh= rwb.getSheet(0)
int rowCount= sh.getRows()
SimpleDateFormat sdf= new SimpleDateFormat("dd/MM/yyyy")
book= new Book[rowCount - 1]
for (int i= 1i <rowCounti++)
{
book[i - 1]= new Book()
jxl.Cell[] ce= sh.getRow(i)
book[i - 1].setIsbn(ce[0].getContents().toString())
book[i - 1].setSeries(ce[1].getContents().toString())
book[i - 1].setBookTitle(ce[2].getContents().toString())
book[i - 1].setWriter(ce[3].getContents().toString())
book[i - 1].setTranscribe(ce[4].getContents().toString())
book[i - 1].setPublisher(ce[5].getContents().toString())
book[i - 1].setPublishDate(sdf.parse(ce[6].getContents().toString(), new ParsePosition(0)))
book[i-1].setVersion(Integer.parseInt(ce[7].getContents().toString()))
book[i-1].setPage(Integer.parseInt(ce[8].getContents().toString()))
book[i-1].setCls(ce[9].getContents().toString())
book[i-1].setPrecisnotes(ce[10].getContents().toString())
book[i-1].setInstall(ce[11].getContents().toString())
book[i-1].setPrice(Float.parseFloat(ce[12].getContents().toString()))
book[i-1].setUserid(usb.getUser().getUserid())
getVector().addElement(book[i - 1])
}
rwb.close()
fis.close()
} catch (FileNotFoundException e)
{} catch (BiffException e)
{} catch (IOException e)
{} catch (NumberFormatException e)
{
ShowMessage("数据导入失败，请按照本软件要求的EXCEL格式导入定单")
}
}
1、利用Excel第三方工具，将Excel文件读取到内存中。使用最简单，方便的工具是apache的poi工具包，自己网上下载 http://poi.apache.org/ ，使用方法网上一搜一大片。
2、如果是对于特别大的excel（大于20M的话），简单的读取方法就容易内存溢出了，需要采用流式读取的方式，参考http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api
3、将已读入内存的Excel数据，整理成写数据库的数据结构，然后插入数据库。这部分工作应该不用介绍了，就是基本的数据库 *** 作方法，与excel无关了
具体如下：
1、简介
编程是编写程序的中文简称，就是让计算机代为解决某个问题，对某个计算体系规定一定的运算方式，是计算体系按照该计算方式运行，并最终得到相应结果的过程。
为了使计算机能够理解人的意图，人类就必须将需解决的问题的思路、方法和手段通过计算机能够猜宽郑理解的形式告诉计算机，使得计算机能够根据人的指令一步一步去工作，完成某种特定的任务。这种人和计算体系之间交流的过程就是编程。
2、汇编程序
汇编程序。使用汇编语言编写计算机程序，程序员仍然需要十分熟悉计算机系统的硬件结构，所以从程序设计本身上来看仍然是低效率的、繁琐的。但正是由于汇编语言与计算机硬件系统关系密巧野切，在某些特定的场合，如对时空效率要求很高的系统核心程序以及实时控制程序等，迄今为止汇编语言仍然是十分有效的程序设计工具。
3、执行原理
计算机对除机穗颂器语言以外的源程序不能直接识别、理解和执行，都必须通过某种方式转换为计算机能够直接执行的。这种将高级编程硬件程序设计语言编写的源程序转换到机器目标程序的方式有两种:解释方式和编译方式。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）