½«Excelת»»³ÉsqlÎļþ£¬²åÈëÊý¾Ý¿â
ÐèÇó£ºÓÐexcelÎļþ£¬º¬¶à¸ösheet£¬Ã¿¸ösheetµÄÄÚÈݶÔÓ¦²åÈëµ½Ò»ÕÅ±í£¬sheetµÄÃû³Æ¾ÍÊǶÔÓ¦µÄ±íÃû³Æ¡£
ÿһÐÐΪÁÐÃû£¬ÀýÈ磺
´ï³É£º½«Ã¿¸ösheetÊä³ö³ÉÒ»¸öÒÔsheetÃû³ÆÃüÃûµÄsqlÎļþ£¬ÄÚÈÝΪÿÐÐÄÚÈݵÄinsertÓï¾ä¡£
ÒÔÉÏͼΪÀý»áÉú³ÉÈý¸ösqlÎļþ£¬·Ö±ðÊÇTF_R_TERMINAL_ARCH.sql£¬ TF_R_STOCK_TRADE.sql ºÍ TF_R_STOCK_TRADE_DETAIL.sql ÈçÏÂͼ
ÏÂÃæÊdzÌÐòExcelToInsert.java
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelToInsert {
public static void main(String[] args) {
String table_name = ""; // ±íÃû
String sqlCell = ""; // ±íµ¥Ôª¸ñ
String SQL = ""; // ÍêÕûµÄÒ»ÌõSQL²åÈëÓï¾ä
final String EXL_NAME = "20100419"; // ExcelÎļþÃû
final String BASE_PATH = "F:/temp/"; // Îļþ·¾¶
final String IN_EXL_PATH = BASE_PATH + EXL_NAME + ".xls"; // excelPath
FileWriter fw = null;
int rows = 0;
int columns = 0;
try {
try {
Workbook rwb = Workbook.getWorkbook(new File(IN_EXL_PATH));
Sheet rs[] = rwb.getSheets();
// ±éÀúsheet
for (int i = 0; i < rs.length; i++) {
table_name = rwb.getSheetNames()[i]; // ±íÃûÈ¡sheetName
fw = new FileWriter(BASE_PATH + table_name + ".sql");
String preSql = "INSERT INTO TABLE "; // insertÓï¾äµÄÇ°°ë²¿·Ý
preSql += table_name + "(";
rows = rs[i].getRows();
columns = rs[i].getColumns();
// ±éÀúÐÐ
for (int j = 0; j < rows; j++) {
String sufSql = " VALUES( ";
if (j == 0) {// µÚÒ»ÐУ¬ÓÃÓÚÈ¡ÁÐÃû£¬ÔìinsertÓï¾äÇ°°ë²¿·Ý£¬Õⲿ·Ý¶ÔÓÚͬһÕűíÊÇÏàͬµÄ
for (int g = 0; g < columns - 1; g++) {
sqlCell = rs[i].getCell(g, 0).getContents().trim();
preSql += sqlCell + ",";
}
// insertÓï¾äÇ°°ë²¿·ÝÉú³É
preSql += rs[i].getCell(columns - 1, 0).getContents().trim()+ ") ";
}
// ÆäËüÐУ¬È¡¾ßÌåinsertµÄÄÚÈÝ£¬¼ÈinsertÓï¾äµÄºó°ë²¿·Ý
else {
for (int g = 0; g < columns - 1; g++) {
sqlCell = rs[i].ge
Ïà¹ØÎĵµ£º
ÇÉÓÃSQLµÄÈ«¾ÖÁÙʱ±í·ÀÖ¹Óû§Öظ´µÇ¼
ÎÄÕÂÀ´×Ô£ºhttp://www.cnblogs.com/lindayyh/archive/2010/04/05/1704763.html
ÔÚÎÒÃÇ¿ª·¢ÉÌÎñÈí¼þµÄʱºò£¬³£³£»áÓöµ½ÕâÑùµÄÒ»¸öÎÊÌ⣺ÔõÑù·ÀÖ¹Óû§Öظ´µÇ¼ÎÒÃǵÄϵͳ£¿ÌرðÊǶÔÓÚÒøÐлòÊDzÆÎñ²¿ÃÅ£¬¸üÊÇÒªÏÞÖÆÓû§ÒÔÆ乤ºÅÉí·Ý¶à´ÎµÇÈë¡£
¿ÉÄÜ»áÓÐÈË˵ÔÚÓû§ÐÅÏ¢±íÖмÓÒ»×Ö¶ÎÅÐ ......
1 Âß¼Êý¾Ý¿âºÍ±íµÄÉè¼Æ
Êý¾Ý¿âµÄÂß¼Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØϵÊÇÓÅ»¯¹ØϵÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪ
ÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£
±ê×¼»¯µÄÊý¾Ý¿âÂß¼Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØϵµÄÕ±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯
±íµÄһЩºÃ´¦¡£
A:ÓÉÓÚ±íÕ£¬Òò´Ë¿ÉÒÔʹŠ......
µÚÒ»Õ£ºÐÅÏ¢Ìåϵ½á¹¹ÔÔò
¸ù¾ÝÒÔÏÂ7¸öÏ໥ÒÀÀµµÄÊý¾Ý´æ´¢Ä¿±êÉè¼ÆºÍÆÀ¹ÀÈκÎÊý¾Ý´æ´¢£º
l ¼òµ¥ÐÔ£»
l ÓÐÓÃÐÔ
l Êý¾ÝÍêÕûÐÔ
l ÐÔÄÜ
l ¿ÉÓÃÐÔ
l ¿ÉÀ©Õ¹ÐÔ
l °²È«ÐÔ
¼Ü¹¹Éè¼ÆÔÔò
l ±ÜÃâ¹ýÓÚ¸´ÔÓ
l ¾«ÐÄÌôÑ¡¼ü
l Ê÷Á¢¿ÉÑ¡Êý¾Ý
l ÊµÏ ......
ÎÊÌâ:
Äú¹¤×÷µÄ±¾»ú×°ÓÐVisual Studio 2005£¬¾ÖÓòÍøÖÐÓÐһ̨SQL Server 2005Êý¾Ý¿â·þÎñÆ÷£¬ÄãÏëͨ¹ý±¾»úÔ¶³Ìµ÷ÊÔSQL Server 2005·þÎñÆ÷ÉϵĴ洢¹ý³Ì¡£µ«ÊDz»ÖªµÀÈçºÎÅäÖûòÆôÓÃÔ¶³Ìµ÷ÊÔ£¿Ï£ÍûÕâƪÎÄÕ¶ÔÄúÓÐÓ᣶ÔÓÚÊý¾Ý¿âºÍVisual StudioÔÚͬһ»úÆ÷µÄ´æ´¢¹ý³Ìµ÷ÊÔ£¬Ô°×ÓÀïÒѾÓÐһƪÒë×÷˵µÄºÜºÃÁË£¬¿ ......