java實現導入excel數據（java導入excel數據到資料庫）

本文目錄一覽：

• 1、java怎麼批量導入excel數據
• 2、如何在Java中導入Excel表數據
• 3、如何在java程序中導入excel數據
• 4、java使用什麼技術實現excel數據的批量導入導出
• 5、如何把java查詢出的內容導入到excel表格
• 6、java中怎麼把excel導入資料庫

java怎麼批量導入excel數據

兩種方案：1.可以對資料庫中的這張表進行本地緩存處理，驗證時調用緩存進行匹配驗證。2.用程序生成需要導入excel的數據模板，在模板里將要驗證的這一列做成下拉框。此模板條件下的excel數據文件批量導入時即不用校驗。具體採用哪種視你的應

如何在Java中導入Excel表數據

使用jxl這個包導入這個包下的importjxl.Workbook;importjxl.write.Label;importjxl.write.WritableSheet;importjxl.write.WritableWorkbook;然後Filef=newFile(“d:/view.xls”);//獲得文件WritableWorkbookwb=Workbook.createWorkbook(f);//可以讀寫的workbookWritableSheets=wb.createSheet(“第一頁”,0);//workbook中的sheet,就是在excel下面那個sheet1,sheet2.這個方法表名使用第一個sheet並且命名為”第一頁”Labellable=null;//label就是某一個小單元格2層循環,給每個labellabel=newLabel(列,行,值);//都是從0開始的.就是給第幾列地幾行的labe里寫東西s.addCell(l);//把這個lable加入到sheet中最後wb.write();wb.close();讀的也差不多.你百度一下jxl,到處都是例子的哇望採納

如何在java程序中導入excel數據

import java.io.BufferedInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Date;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ReadExcel2 {

/**

* @param args

* @throws IOException

* @throws FileNotFoundException

*/

public static void main(String[] args) throws FileNotFoundException, IOException {

File file = new File(“src/test.xls”);

String[][] data = getData(file, 0);

printStringArray(data);

}

public static void printStringArray(String[][] data){

for(int i =0; i data.length; i++){

for(int j=0; j data[i].length; j++){

System.out.print(data[i][j]+”\t”);

}

System.out.print(“\n”);

}

}

/**

*

* 讀取Excel的內容，第一維數組存儲的是一行中格列的值，二維數組存儲的是多少個行

* @param file 讀取數據的源Excel

* @param ignoreRows 讀取數據忽略的行數，比喻行頭不需要讀入 忽略的行數為1

* @return 讀出的Excel中數據的內容

* @throws FileNotFoundException

* @throws IOException

*/

public static String[][] getData(File file, int ignoreRows)

throws FileNotFoundException, IOException {

ListString[] result = new ArrayListString[]();

int rowSize = 0;

BufferedInputStream in = new BufferedInputStream(new FileInputStream(

file));

// 打開HSSFWorkbook

POIFSFileSystem fs = new POIFSFileSystem(in);

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFCell cell = null;

for (int sheetIndex = 0; sheetIndex wb.getNumberOfSheets(); sheetIndex++) {

HSSFSheet st = wb.getSheetAt(sheetIndex);

// 第一行為標題，不取

for (int rowIndex = ignoreRows; rowIndex = st.getLastRowNum(); rowIndex++) {

HSSFRow row = st.getRow(rowIndex);

if (row == null) {

continue;

}

int tempRowSize = row.getLastCellNum() + 1;

if (tempRowSize rowSize) {

rowSize = tempRowSize;

}

String[] values = new String[rowSize];

Arrays.fill(values, “”);

boolean hasValue = false;

for (short columnIndex = 0; columnIndex = row.getLastCellNum(); columnIndex++) {

String value = “”;

cell = row.getCell(columnIndex);

if (cell != null) {

// 注意：一定要設成這個，否則可能會出現亂碼

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:

value = cell.getStringCellValue();

break;

case HSSFCell.CELL_TYPE_NUMERIC:

if (HSSFDateUtil.isCellDateFormatted(cell)) {

Date date = cell.getDateCellValue();

if (date != null) {

value = new SimpleDateFormat(“yyyy-MM-dd”)

.format(date);

} else {

value = “”;

}

} else {

value = new DecimalFormat(“0”).format(cell

.getNumericCellValue());

}

break;

case HSSFCell.CELL_TYPE_FORMULA:

// 導入時如果為公式生成的數據則無值

if (!cell.getStringCellValue().equals(“”)) {

value = cell.getStringCellValue();

} else {

value = cell.getNumericCellValue() + “”;

}

break;

case HSSFCell.CELL_TYPE_BLANK:

break;

case HSSFCell.CELL_TYPE_ERROR:

value = “”;

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

value = (cell.getBooleanCellValue() == true ? “Y”

: “N”);

break;

default:

value = “”;

}

}

if (columnIndex == 0 value.trim().equals(“”)) {

break;

}

values[columnIndex] = rightTrim(value);

hasValue = true;

}

if (hasValue) {

result.add(values);

}

}

}

in.close();

String[][] returnArray = new String[result.size()][rowSize];

for (int i = 0; i returnArray.length; i++) {

returnArray[i] = (String[]) result.get(i);

}

return returnArray;

}

/**

*

* 去掉字元串右邊的空格

* @param str 要處理的字元串

* @return 處理後的字元串

*/

public static String rightTrim(String str) {

if (str == null) {

return “”;

}

int length = str.length();

for (int i = length – 1; i = 0; i–) {

if (str.charAt(i) != 0x20) {

break;

}

length–;

}

return str.substring(0, length);

}

}

java使用什麼技術實現excel數據的批量導入導出

java使用第三方工具包POI技術實現excel數據的批量導入導出。

舉例如下：

1、下載apache的相關jar包。poi-ooxml-3.6.jar xmlbeans-2.3.0.jar等，如圖：

2、編寫相關的讀寫類

  /**

* 讀取xls文件內容

*/

private

ListXlsDto readXls() throws

IOException {

InputStream is = new

FileInputStream(“test.xls”);

HSSFWorkbook hssfWorkbook = new

HSSFWorkbook(is);

XlsDto xlsDto = null;

ListXlsDto list = new

ArrayListXlsDto();

// 循環工作表Sheet

for

(int numSheet = 0; numSheet hssfWorkbook.getNumberOfSheets(); numSheet++) {

HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);

if

(hssfSheet == null) {

continue;

}

// 循環行Row

for

(int rowNum = 1; rowNum = hssfSheet.getLastRowNum(); rowNum++) {

HSSFRow hssfRow = hssfSheet.getRow(rowNum);

if

(hssfRow == null) {

continue;

}

xlsDto = new

XlsDto();

// 循環列Cell

// 0學號 1姓名 2學院 3課程名 4 成績

// for (int cellNum = 0; cellNum =4; cellNum++) {

HSSFCell xh = hssfRow.getCell(0);

if

(xh == null) {

continue;

}

xlsDto.setXh(getValue(xh));

HSSFCell xm = hssfRow.getCell(1);

if

(xm == null) {

continue;

}

xlsDto.setXm(getValue(xm));

HSSFCell yxsmc = hssfRow.getCell(2);

if

(yxsmc == null) {

continue;

}

xlsDto.setYxsmc(getValue(yxsmc));

HSSFCell kcm = hssfRow.getCell(3);

if

(kcm == null) {

continue;

}

xlsDto.setKcm(getValue(kcm));

HSSFCell cj = hssfRow.getCell(4);

if

(cj == null) {

continue;

}

xlsDto.setCj(Float.parseFloat(getValue(cj)));

list.add(xlsDto);

}

}

return

list;

}

3、導出就是輸入到一個新的excel文件裡面

public void writeXls(ListStudent list, String path) throws Exception {

  if (list == null) {原始數據為空，直接返回

      return;

  }

  int countColumnNum = list.size();//設置列數

  HSSFWorkbook book = new HSSFWorkbook(); //創建工作表對象

  HSSFSheet sheet = book.createSheet(“studentSheet”);

  // 創建第一行

  HSSFRow firstRow = sheet.createRow(0);

  HSSFCell[] firstCells = new HSSFCell[countColumnNum];

  //創建表頭

  String[] options = { “no”, “name”, “age”, “score” };

 //循環數據域

  for (int j = 0; j options.length; j++) {

      firstCells[j] = firstRow.createCell(j);

      firstCells[j].setCellValue(new HSSFRichTextString(options[j]));

  }

  //處理每一個cell的值

  for (int i = 0; i countColumnNum; i++) {

      HSSFRow row = sheet.createRow(i + 1);

      Student student = list.get(i);

      for (int column = 0; column options.length; column++) {

          HSSFCell no = row.createCell(0);

          HSSFCell name = row.createCell(1);

          HSSFCell age = row.createCell(2);

          HSSFCell score = row.createCell(3);

          no.setCellValue(student.getNo());

          name.setCellValue(student.getName());

          age.setCellValue(student.getAge());

          score.setCellValue(student.getScore());

      }

  }

  File file = new File(path);

  OutputStream os = new FileOutputStream(file);

  System.out.println(Common.WRITE_DATA + path);

  book.write(os);

  os.close();

}

如何把java查詢出的內容導入到excel表格

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= 0; i 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= 1; i rowCount; i++)

{

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格式導入定單”);

}

}

java中怎麼把excel導入資料庫

1、利用Excel第三方工具，將Excel文件讀取到內存中。使用最簡單，方便的工具是apache的poi工具包，自己網上下載  ，使用方法網上一搜一大片。

2、如果是對於特別大的excel（大於20M的話），簡單的讀取方法就容易內存溢出了，需要採用流式讀取的方式，參考 

3、將已讀入內存的Excel數據，整理成寫資料庫的數據結構，然後插入資料庫。這部分工作應該不用介紹了，就是基本的資料庫操作方法，與excel無關了

具體如下：

1、簡介

編程是編寫程序的中文簡稱，就是讓計算機代為解決某個問題，對某個計算體系規定一定的運算方式，是計算體系按照該計算方式運行，並最終得到相應結果的過程。

為了使計算機能夠理解人的意圖，人類就必須將需解決的問題的思路、方法和手段通過計算機能夠理解的形式告訴計算機，使得計算機能夠根據人的指令一步一步去工作，完成某種特定的任務。這種人和計算體系之間交流的過程就是編程。

2、彙編程序

彙編程序。使用彙編語言編寫計算機程序，程序員仍然需要十分熟悉計算機系統的硬體結構，所以從程序設計本身上來看仍然是低效率的、繁瑣的。但正是由於彙編語言與計算機硬體系統關係密切，在某些特定的場合，如對時空效率要求很高的系統核心程序以及實時控制程序等，迄今為止彙編語言仍然是十分有效的程序設計工具。

3、執行原理

計算機對除機器語言以外的源程序不能直接識別、理解和執行，都必須通過某種方式轉換為計算機能夠直接執行的。這種將高級編程硬體程序設計語言編寫的源程序轉換到機器目標程序的方式有兩種:解釋方式和編譯方式。