本文目錄一覽：
- 1、如何用java完成Excel快速的導入導出
- 2、java開發怎麼導入導出excel裡面信息內容？
- 3、怎麼用java實現mysql數據庫的導入導出
- 4、java如何實現導入和導出功能
- 5、java使用什麼技術實現excel數據的批量導入導出
- 6、如何用java實現mysql數據庫的導入導出
如何用java完成Excel快速的導入導出
導出
現在我們定義好對象了,如何導出Excel –ExcelExportUtil 這個導出工具類
public void testExportExcel_1() throws Exception {
ExportParams params = new ExportParams(“0328課程表”, “日期：2016-03-28”, “六年一班”);
Workbook workbook = ExcelExportUtil.exportExcel(params, CourseEntity.class,courseList);
FileOutputStream fos = new FileOutputStream(“D:/excel/0328課程表.xls”);
workbook.write(fos);
fos.close();
}
我們只要把我們定義好的對象的class傳進去,以及對象的集合,Easypoi就可以返回一個Excel的workbook了,同時Easypoi是兼容03版本office和07版本office,你要穿個參數指定下類型及可以了,是不是不是比我們自己寫代碼簡單多了,最少只需要2行代碼就可以完成我們的office操作了
導入
我們把導出寫完了,導入是不是很複雜呢,也不是,導入也是同樣簡單,定時實體和上面定義的方式一樣
導入是用導入工具類
ImportParams params = new ImportParams();
params.setHeadRows(2);
ListCourseEntity list = ExcelImportUtil.importExcel(inputStream, CourseEntity.class, params);
定義下表頭的參數,然後把流傳入進去就可以得到我們的對象列表是不是so easy.趕快來使用吧
java開發怎麼導入導出excel裡面信息內容？
public class ExcelExport {
/**
* 默認每個sheet頁最多顯示的行數
*/
private static final int sheet_rows = 50000;
/**
* 導出Excel文件
*
* @param titleList
* 表頭信息
* @param dataList
* 表格數據
* @param fileName
* 導出文件完整名稱 demo.xls
* @param request
* @param response
* @throws IOException
*/
public static void exportExcelFile(ListString titleList,
ListListString dataList, String fileName,
HttpServletRequest request, HttpServletResponse response)
throws IOException {
HSSFWorkbook workBook = exportDataToExcel(titleList, dataList);
response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
response.setCharacterEncoding(“UTF-8”);
fileName = encodeFilename(fileName, request);
response.setHeader(“Content-disposition”, “attachment;filename=” + fileName);
workBook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
public static String encodeFilename(String filename, HttpServletRequest request) {
String agent = request.getHeader(“USER-AGENT”);
try {
if ((agent != null) ( 0 = agent.indexOf(“Firefox”))) {
return MimeUtility.encodeText(filename, “UTF-8”, “B”);
} else if((agent != null) ( 0 = agent.indexOf(“Chrome”))){
return filename = new String(filename.getBytes(), “ISO8859-1”);
}
else {
if (agent != null) {
String newFileName = URLEncoder.encode(filename, “UTF-8”);
newFileName = StringUtils.replace(newFileName, “+”, “%20”);
if (newFileName.length() 150) {
newFileName = new String(filename.getBytes(“GB2312”),
“ISO8859-1”);
newFileName = StringUtils.replace(newFileName, ” “,
“%20”);
}
return newFileName;
}
}
} catch (Exception ex) {
return filename;
}
return filename;
}
public static HSSFWorkbook exportDataToExcel(ListString titleList, ListListString dataList) {
/* 1.創建一個Excel文件 */
HSSFWorkbook workbook = new HSSFWorkbook();
/* 2.創建Excel的一個Sheet */
HSSFSheet sheet = workbook.createSheet();
/* 3.創建表頭凍結 */
sheet.createFreezePane(0, 1);
/* 4.設置列寬 */
for (int i = 0; i titleList.size(); i++) {
sheet.setColumnWidth(i, 5000);
}
/* 5.表頭字體 */
HSSFFont headfont = workbook.createFont();
headfont.setFontName(“宋體”);
headfont.setFontHeightInPoints((short) 12);// 字體大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
/* 6.表頭樣式 */
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
// 設置背景色為藍色
headstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
headstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
/* 7.普通單元格字體 */
HSSFFont font = workbook.createFont();
font.setFontName(“宋體”);
font.setFontHeightInPoints((short) 12);
/* 8.普通單元格樣式 */
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
/* 9. 拼裝表頭 */
IteratorString titleRowIterator = titleList.iterator();
int columnIndex = 0;
HSSFRow row = sheet.createRow(0);
while (titleRowIterator.hasNext()) {
String cellValue = titleRowIterator.next();
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cellValue);
cell.setCellStyle(headstyle);
columnIndex++;
cell = null;
}
/* 10.組織表數據 */
IteratorListString rowIterator = dataList.iterator();
int rowIndex = 1;
while (rowIterator.hasNext()) {
ListString columnList = rowIterator.next();
row = sheet.createRow(rowIndex);
IteratorString columnIterator = columnList.iterator();
columnIndex = 0;
while (columnIterator.hasNext()) {
String cellValue = columnIterator.next();
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cellValue);
cell.setCellStyle(style);
cell = null;
columnIndex++;
}
row = null;
rowIndex++;
}
return workbook;
}
/**
* 重載導出數據到Excel中
* @param titleList 表頭
* @param dataList 表數據
* @param amount 每個sheet頁顯示行數
* @return
*/
public static HSSFWorkbook exportDataToExcel(ListString titleList, ListListString dataList, int amount) {
/* 1.創建一個Excel文件 */
HSSFWorkbook workbook = new HSSFWorkbook();
//校驗傳入的參數
if(titleList==null){
titleList = new ArrayListString();
}
//無數據直接返回
if(dataList==null || dataList.size()==0){
return workbook;
}
//傳入數據不正確，按照默認條數顯示
if(amount65535 || amount=0){
amount = sheet_rows;
}
//獲取sheet頁的數量
int row_num = 0;
int y = dataList.size()%amount;
if(y == 0){
row_num = dataList.size()/amount;
}else{
row_num = dataList.size()/amount + 1;
}
/* 表頭字體 */
HSSFFont headfont = workbook.createFont();
headfont.setFontName(“宋體”);
headfont.setFontHeightInPoints((short) 12);// 字體大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
/* 表頭樣式 */
HSSFCellStyle headstyle = workbook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
// 設置背景色為藍色
headstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
headstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
/* 普通單元格字體 */
HSSFFont font = workbook.createFont();
font.setFontName(“宋體”);
font.setFontHeightInPoints((short) 12);
/* 普通單元格樣式 */
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
//循環寫入每個sheet頁
for(int i=0;irow_num;i++){
/* 創建Excel的一個Sheet */
HSSFSheet sheet = workbook.createSheet();
/* 創建表頭凍結 */
sheet.createFreezePane(0, 1);
/* 設置列寬 */
for (int t = 0; t titleList.size(); t++) {
sheet.setColumnWidth(t, 5000);
}
/* 拼裝表頭 */
IteratorString titleRowIterator = titleList.iterator();
int columnIndex = 0;
HSSFRow row = sheet.createRow(0);
while (titleRowIterator.hasNext()) {
String cellValue = titleRowIterator.next();
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cellValue);
cell.setCellStyle(headstyle);
columnIndex++;
cell = null;
}
/* 組織表數據 */
int rowIndex = 1;
for (int j=amount*i;(jamount*(i+1)jdataList.size());j++) {
ListString columnList = dataList.get(j);
row = sheet.createRow(rowIndex);
IteratorString columnIterator = columnList.iterator();
columnIndex = 0;
while (columnIterator.hasNext()) {
String cellValue = columnIterator.next();
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cellValue);
cell.setCellStyle(style);
cell = null;
columnIndex++;
}
row = null;
rowIndex++;
}
}
return workbook;
}
/**
* 重載導出Excel功能,新增一項amount每個sheet導出記錄行數
* @param titleList
* @param dataList
* @param fileName
* @param amount 行數如果小於等於0或大於65535則按照默認顯示
* @param request
* @param response
* @throws IOException
*/
public static void exportExcelFile(ListString titleList,
ListListString dataList, String fileName, int amount,
HttpServletRequest request, HttpServletResponse response)
throws IOException {
HSSFWorkbook workBook = exportDataToExcel(titleList, dataList, amount);
response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
response.setCharacterEncoding(“UTF-8”);
fileName = encodeFilename(fileName, request);
response.setHeader(“Content-disposition”, “attachment;filename=” + fileName);
workBook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
}
說明：main方法里的第一個參數為要導出的表頭信息，
第二個參數為數據信息，
第三個參數設置文件名，設置好以後直接調用就可以導出Excle了;
導入就是把文件存到服務器，可用Mogodb等數據庫存儲，以下是讀取已存根據存儲的文件id解析Excle數據的大致過程：
POIFSFileSystem fs = null;
if (!StringUtils.isBlank(fileId)) {
fs = new POIFSFileSystem(new ByteArrayInputStream(
//傳入對應的文件對象
MongoFileUtil.readFile(fileId)));
}
// 構造 XSSFWorkbook 對象，filePath 傳入文件路徑
HSSFWorkbook xwb = new HSSFWorkbook(fs);
// 讀取第一個sheet
HSSFSheet sheet = xwb.getSheetAt(0);
for (int i = sheet.getFirstRowNum() + 1; i sheet.getPhysicalNumberOfRows();i++){
//解析每行的數據
HSSFRow singleRow = sheet.getRow(i);
//解析每個單元格數據
singleRow.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String cellValue= singleRow.getCell(0).getStringCellValue();
}
怎麼用java實現mysql數據庫的導入導出
使用Java實現對MySql數據庫的導入與導出
package com.project.ajaxs;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.Calendar;
import java.util.Date;
public class BakMysql {
//main的方法，主要是我用於測試的，是想着取得CLASS的路徑，然後備份的文件寫在服務器的類路徑下
public static void main(String[] args) {
BakMysql bk = new BakMysql();
bk.backup();
// bk.load();
}
//backup方法是備份數據庫到服務器地址
public void backup() {
Calendar now = Calendar.getInstance();
String name = now.getTime()+””+(now.getTime().getMonth()+1)+””+now.getTime().getDate();
String filename = name.substring(24)+” “+name.substring(11,13)+””+name.substring(14,16)+””+name.substring(17,19);
try {
String filePath =”e:/project”+filename+”.sql”;
Runtime rt = Runtime.getRuntime();
// 調用 mysql 的 cmd:
Process child = rt.exec(“C:/Program Files/MySQL/MySQL Server 5.0/bin/mysqldump.exe -uroot -p8095longchun project”);// 設置導出編碼為utf8。這裡必須是utf8
//注意這一句，是指運行mysqldump命令，後面跟的是登錄名和登錄的密碼，接着後面的是指備份的數據庫的名字，到此結束，以此生成一個執行的進程，取得此進程的輸出流到我們要備份的文件
// 把進程執行中的控制台輸出信息寫入.sql文件，即生成了備份文件。註：如果不對控制台信息進行讀出，則會導致進程堵塞無法運行
InputStream in = child.getInputStream();// 控制台的輸出信息作為輸入流
InputStreamReader xx = new InputStreamReader(in, “utf-8”);// 設置輸出流編碼為utf8。這裡必須是utf8，否則從流中讀入的是亂碼
String inStr;
StringBuffer sb = new StringBuffer(“”);
String outStr;
// 組合控制台輸出信息字符串
BufferedReader br = new BufferedReader(xx);
while ((inStr = br.readLine()) != null) {
sb.append(inStr + “\r\n”);
}
outStr = sb.toString();//備份出來的內容是一個字條串
// 要用來做導入用的sql目標文件：
FileOutputStream fout = new FileOutputStream(filePath);
OutputStreamWriter writer = new OutputStreamWriter(fout, “utf8”);
writer.write(outStr);//寫文件
// 註：這裡如果用緩衝方式寫入文件的話，會導致中文亂碼，用flush()方法則可以避免
writer.flush();
// 別忘記關閉輸入輸出流
in.close();
xx.close();
br.close();
writer.close();
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//數據庫的導入
public void load() {
try {
String fPath = “e:/aa.sql”;
Runtime rt = Runtime.getRuntime();
Process child = rt.exec(“C:/Program Files/MySQL/MySQL Server 5.0/bin/mysqladmin.exe -uroot -p8095longchun create project”);
Process child1 =rt.exec(“C:/Program Files/MySQL/MySQL Server 5.0/bin/mysql.exe -uroot -p8095longchun project”);
OutputStream out = child1.getOutputStream();//控制台的輸入信息作為輸出流
String inStr;
StringBuffer sb = new StringBuffer(“”);
String outStr;
BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(fPath), “utf-8”));
while ((inStr = br.readLine()) != null) {
sb.append(inStr + “\r\n”);
}
outStr = sb.toString();
OutputStreamWriter writer = new OutputStreamWriter(out, “utf8”);
writer.write(outStr);
// 註：這裡如果用緩衝方式寫入文件的話，會導致中文亂碼，用flush()方法則可以避免
writer.flush();
out.close();
br.close();
writer.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
原文來自
java如何實現導入和導出功能
看你要導入什麼了，什麼格式，是電話還是其他軟件或者資料，只要兼容就能導入導出的。
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實現mysql數據庫的導入導出
要使用CMD命令窗口首先的將MYSQL環境變量設置到操作系統中,這是第一步,
導出MYSQL數據的命令如下:
mysqldump -u root [-p password] database [table] x.sql
注:root的登錄數據庫的用戶名；database為使用到的數據庫；table為表名；x.sql為導出的sql腳本
這樣導出為一個SQL腳本文件,裡面包括所有的建表語句與數據插入語句,
而數據導入也差不多,給傳入一個SQL腳本,語句如下:
source d:/mysql.sql;
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/241016.html
微信掃一掃 
支付寶掃一掃 