用C#代码写一个数据库导入系统,只是将excel表导入到SQL2008数据库中,
我用的是offices 2010
前台是一个fileupload控件,一个button控件,选择excel文件,点击button按钮,直接把excel表中的内容导入到SQL数据库中。请问怎么实现这个功能?详细一些,谢谢!SQLC#Excel导入
我用的是offices 2010
前台是一个fileupload控件,一个button控件,选择excel文件,点击button按钮,直接把excel表中的内容导入到SQL数据库中。请问怎么实现这个功能?详细一些,谢谢!SQLC#Excel导入
OleDbConnection conn = new OleDbConnection(strConn);
select * from sheetName
static public DataSet ExcelToDataSet(string filename)//读取
{
try
{
DataSet ds;
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=Excel 8.0;" +
"data source=" + filename;
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT 中药名称,中药分类(植物、动物、矿物和其他),拼音,拼音缩写 FROM [Sheet1$]";
myConn.Open();//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); //创建一个 DataSet对象
ds = new DataSet(); //得到自己的DataSet对象
myCommand.Fill(ds);
myConn.Close();
return ds;
}
catch (Exception e)
{
return null;
}
}
{
string strReturn = "success"; //判断Excel是否存在,不存在就创建
checkExcelExists(strFilePath, strFileName, strPageName, strHeads, boolAppend);
try
{
HSSFWorkbook hssfworkbook;
using (FileStream fs = new FileStream(strFilePath + "\\" + strFileName, FileMode.Open))
{
hssfworkbook = new HSSFWorkbook(fs);
fs.Close();
}
Sheet sheet = hssfworkbook.GetSheetAt(0);
int intCurrentRow = sheet.LastRowNum + 1; for (int i = 0; i < dt.Rows.Count; i++)
{
Row row = sheet.CreateRow(i + intCurrentRow);
for (int j = 0; j < strHeads.Length; j++)
{
Cell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][strFields[j]].ToString());
}
}
sheet.ForceFormulaRecalculation = true; using (FileStream fs = new FileStream(strFilePath + "\\" + strFileName, FileMode.Create))
{
hssfworkbook.Write(fs);
fs.Close();
}
}
catch (Exception Ex)
{
LogHelper.Error("导出Excel格式 失败!", Ex);
strReturn = Ex.Message;
} return strReturn;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook("c://11.xls");
foreach (Worksheet worksheet in workbook.Worksheets)
{}
利用button获取到excel的路径然后读取其内容存到一个datatable里的代码我有我问的是具体的inert into到数据库的操作,我只能导入一个空表进去,内容没法导进去
{
if ((txtFilePath.HasFile))
{ OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string query = null;
string connString = "";
string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower(); //Check file type
if (strFileType == ".xls" || strFileType == ".xlsx")
{
txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
}
else
{
return;
}
string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + "; Extended Properties=\"Excel 8.0;HDR=no;IMEX=1;\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=no;IMEX=1;\"";
} query = "SELECT * FROM [Sheet1$]";
//Create the connection object
conn = new OleDbConnection(connString);
//Open connection
if (conn.State == ConnectionState.Closed) conn.Open();
//Create the command object
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds); dtgJobs.DataSource = ds.Tables[0];
dtgJobs.DataBind();
//判断是否输入表的名称
string tablename = "";
if (this.tableName.Text == "")
{
tablename = txtFilePath.FileName.ToString().Trim().Substring(0, txtFilePath.FileName.ToString().Trim().LastIndexOf("."));
}
else
{
tablename = this.tableName.Text;
} /*using (SqlConnection con = new SqlConnection(""))
{
SqlDataAdapter daa = new SqlDataAdapter("", con); daa.Update(ds);
}*/ //创建新表
CreateTable(tablename, ds);
lblMessage.Text = "Data retrieved successfully! Total Records:" + ds.Tables[0].Rows.Count;
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Visible = true; da.Dispose();
conn.Close();
conn.Dispose();
}
else
{
lblMessage.Text = "Please select an excel file first";
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Visible = true;
}
}
这里的creattable怎么写啊
DataTable dtExcel = ds.Tables[0];
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
string 信息1= dtExcel.Rows[i]["你的列名"].ToString();
string 信息2= dtExcel.Rows[i]["你的列名"].ToString();
//等等
然后存到数据库里 这个你会把???
INSERT INTO XXXXXX VALUES();就好了