Excel2003，2007，2010，2013导入导出sqlserver
来源：互联网 发布：js new一个女朋友 编辑：程序博客网 时间：2024/05/22 01:41
添加Microsoft.Office.Interop.Excel引用。
ExcelIO类：
using System;using System.Data;using System.Data.OleDb;//using System.Collections.Generic;//using System.Text;using Excel = Microsoft.Office.Interop.Excel;using System.Runtime.InteropServices;using System.Diagnostics;using System.Reflection;using System.Collections;namespace com.Common.Utility{ /////////////////////////////////////////////////////////////////////////// //Purpose：Excel文件导入导出,需引用Microsoft Excel 11.0 Object Library //Author: Dangmy //Date: 2007-03-09 //Version: 1.0 /////////////////////////////////////////////////////////////////////////// public class ExcelIO { private int _ReturnStatus; private string _ReturnMessage; public ExcelIO() { } /// <summary> /// 执行返回状态 /// </summary> public int ReturnStatus { get { return _ReturnStatus; } } /// <summary> /// 执行返回信息 /// </summary> public string ReturnMessage { get { return _ReturnMessage; } } /// <summary> /// 导入EXCEL文件到DataSet /// </summary> /// <param name="fileName">Excel全路径文件名</param> /// <returns>导入成功的DataSet</returns> public DataSet SubImportExcel(string fileName) { //判断是否安装EXCEL Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { _ReturnStatus = -1; _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel"; return null; } //判断文件是否被其他进程使用 Excel.Workbook workbook; try { workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); } catch { _ReturnStatus = -1; _ReturnMessage = "Excel文件处于打开状态，请保存关闭"; return null; } //获得所有Sheet名称 int n = workbook.Worksheets.Count; string[] SheetSet = new string[n]; //System.Collections.ArrayList al = new System.Collections.ArrayList(); for (int i = 1; i <= n; i++) { SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name; } //释放Excel相关对象 workbook.Close(null, null, null); xlApp.Quit(); if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } GC.Collect(); string strEName = fileName.Substring(fileName.LastIndexOf(".") + 1, (fileName.Length - fileName.LastIndexOf(".") - 1)); //把EXCEL导入到DataSet DataSet ds = new DataSet(); string connStr = string.Empty; if (strEName == "xls") { connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties='Excel 8.0;HDR=YES'"; } else if (strEName == "xlsx") { connStr = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source= " + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES'"; } else { return null; } using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); OleDbDataAdapter da; for (int i = 1; i <= n; i++) { string sql = "select * from [" + SheetSet[i - 1] + "$] "; da = new OleDbDataAdapter(sql, conn); da.Fill(ds, SheetSet[i - 1]); da.Dispose(); } conn.Close(); conn.Dispose(); } return ds; } /// <summary> /// 把Excel导入到DataSet /// </summary> /// <param name="fileName">文件名</param> /// <returns></returns> public DataSet SubImportExcel(string fileName, string[] SheetName) { //判断是否安装EXCEL Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { _ReturnStatus = -1; _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel"; return null; } //判断文件是否被其他进程使用 Excel.Workbook workbook; try { workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); } catch { _ReturnStatus = -1; _ReturnMessage = "Excel文件处于打开状态，请保存关闭"; return null; } //获得所有Sheet名称 int n = workbook.Worksheets.Count; string[] SheetSet = new string[n]; for (int i = 1; i <= n; i++) { SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name; } //释放Excel相关对象 workbook.Close(null, null, null); xlApp.Quit(); if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } GC.Collect(); bool bolSheet = true; for (int i = 0; i < SheetName.Length; i++) { if (!((IList)SheetSet).Contains(SheetName[i])) { bolSheet = false; } } if (bolSheet) { string strEName = fileName.Substring(fileName.LastIndexOf(".") + 1, (fileName.Length - fileName.LastIndexOf(".") - 1)); //把EXCEL导入到DataSet DataSet ds = new DataSet(); string connStr = string.Empty; if (strEName == "xls") { connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties='Excel 8.0;HDR=YES'"; } else if (strEName == "xlsx") { connStr = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source= " + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES'"; } else { return null; } using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); OleDbDataAdapter da; for (int i = 0; i < SheetName.Length; i++) { string sql = "SELECT * FROM [" + SheetName[i] + "$]"; da = new OleDbDataAdapter(sql, conn); da.Fill(ds, SheetName[i]); da.Dispose(); } conn.Close(); conn.Dispose(); } return ds; } else { _ReturnStatus = -1; _ReturnMessage = "Excel文件不存在制定名称的Sheet"; return null; } } /// <summary> /// 把DataTable导出到EXCEL /// </summary> /// <param name="reportName">报表名称</param> /// <param name="dt">数据源表</param> /// <param name="saveFileName">Excel全路径文件名</param> /// <returns>导出是否成功</returns> public bool SubExportExcel(string reportName, System.Data.DataTable dt, string saveFileName) { if (dt == null | dt.Rows.Count == 0) { _ReturnStatus = -1; _ReturnMessage = "数据集为空！"; return false; } bool fileSaved = false; //保存文件 if (saveFileName != "") { DateTime Process_BeforeTime = DateTime.Now; Excel.Application xlApp = new Excel.Application(); DateTime Process_AfterTime = DateTime.Now; if (xlApp == null) { _ReturnStatus = -1; _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel"; return false; } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Excel.Range range; worksheet.Cells.Font.Size = 10; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; worksheet.Cells.NumberFormatLocal = "@"; worksheet.Cells[1, 1] = reportName; ((Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12; ((Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true; //写入字段 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString(); } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; } range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); if (dt.Rows.Count > 0) { range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; } if (dt.Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); fileSaved = true; } catch (Exception ex) { fileSaved = false; _ReturnStatus = -1; _ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message; //WriteInfoToTxt.WriteErrorLog("导出excel" + _ReturnMessage); } finally { //释放Excel对应的对象 workbook.Close(Type.Missing, Type.Missing, Type.Missing); if (xlApp != null) { xlApp.Application.Quit(); xlApp.Quit(); } if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (workbooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; } if (xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } KillExcelProcess(Process_BeforeTime, Process_AfterTime); GC.Collect(); } } else { fileSaved = false; } return fileSaved; } public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName) { bool flag = false; if (SubExportExcel(reportName, dt, saveFileName)) { flag = true; } else { flag = false; } GC.Collect(); return flag; } public DataSet ImportExcel(string fileName) { DataSet ds = SubImportExcel(fileName); GC.Collect(); return ds; } /// <summary> /// 关闭当前Excel进程 /// </summary> /// <param name="Process_BeforeTime"></param> /// <param name="Process_AfterTime"></param> public static void KillExcelProcess(DateTime Process_BeforeTime, DateTime Process_AfterTime) { foreach (Process pro in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { DateTime ProcessBeginTime = pro.StartTime; if ((ProcessBeginTime >= Process_BeforeTime) && (ProcessBeginTime <= Process_AfterTime)) { pro.Kill(); } } } /// <summary> /// 在当前工作表中根据数据生成图表:CreateChart(m_Book, m_Sheet, num); /// </summary> /// <param name="m_Book"></param> /// <param name="m_Sheet"></param> /// <param name="num"></param> //private void CreateChart(Excel._Workbook m_Book, Excel._Worksheet m_Sheet, Excel.Range oResizeRange) private void CreateChart(Excel._Workbook m_Book, Excel._Worksheet m_Sheet, int num) { Excel.Range oResizeRange; Excel.Series oSeries; m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value); m_Book.ActiveChart.ChartType = Excel.XlChartType.xlLine;//设置图形 //设置数据取值范围 m_Book.ActiveChart.SetSourceData(m_Sheet.get_Range("A3", "C" + (num + 1).ToString()), Excel.XlRowCol.xlColumns); //m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, title); //以下是给图表放在指定位置 m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name); oResizeRange = (Excel.Range)m_Sheet.Rows.get_Item(10, Missing.Value); m_Sheet.Shapes.Item(0).Top = (float)(double)oResizeRange.Top; //调图表的位置上边距 oResizeRange = (Excel.Range)m_Sheet.Columns.get_Item(6, Missing.Value); //调图表的位置左边距 // m_Sheet.Shapes.Item(0).Left = (float)(double)oResizeRange.Left; if (num <= 30) { m_Sheet.Shapes.Item(0).Width = 600; } else { m_Sheet.Shapes.Item(0).Width = 20 * num; //调图表的宽度 } m_Sheet.Shapes.Item(0).Height = 250; //调图表的高度 m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19; //设置绘图区的背景色 m_Book.ActiveChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置绘图区边框线条 if (num <= 30) { m_Book.ActiveChart.PlotArea.Width = 600; } else { m_Book.ActiveChart.PlotArea.Width = 20 * num; //设置绘图区宽度 } //m_Book.ActiveChart.ChartArea.Interior.ColorIndex = 10; //设置整个图表的背影颜色 //m_Book.ActiveChart.ChartArea.Border.ColorIndex = 8;// 设置整个图表的边框颜色 m_Book.ActiveChart.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置边框线条 m_Book.ActiveChart.HasDataTable = false; //设置Legend图例的位置和格式 m_Book.ActiveChart.Legend.Top = 20.00; //具体设置图例的上边距 m_Book.ActiveChart.Legend.Left = 60.00;//具体设置图例的左边距 m_Book.ActiveChart.Legend.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone; m_Book.ActiveChart.Legend.Width = 150; m_Book.ActiveChart.Legend.Font.Size = 9.5; //m_Book.ActiveChart.Legend.Font.Bold = true; m_Book.ActiveChart.Legend.Font.Name = "宋体"; //m_Book.ActiveChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;//设置图例的位置 m_Book.ActiveChart.Legend.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置图例边框线条 //设置X轴的显示 Excel.Axis xAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); xAxis.MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot; xAxis.MajorGridlines.Border.ColorIndex = 1;//gridLine横向线条的颜色 xAxis.HasTitle = false; xAxis.TickLabels.Font.Name = "宋体"; xAxis.TickLabels.Font.Size = 9; // .NumberFormatLocal = "0_ " //设置Y轴的显示 Excel.Axis yAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); yAxis.TickLabelSpacing = 30; //yAxis.TickLabels.NumberFormat = "M月D日"; //yAxis.MaximumScaleIsAuto = true; //yAxis.MinimumScaleIsAuto = true; yAxis.TickLabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;//Y轴显示的方向,是水平还是垂直等 yAxis.TickLabels.Font.Size = 8; yAxis.TickLabels.Font.Name = "宋体"; //m_Book.ActiveChart.Floor.Interior.ColorIndex = 8; /***以下是设置标题***** m_Book.ActiveChart.HasTitle=true; m_Book.ActiveChart.ChartTitle.Text = "净值指数"; m_Book.ActiveChart.ChartTitle.Shadow = true; m_Book.ActiveChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous; */ oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(1); oSeries.Name = "在线数"; oSeries.Values = "='" + m_Sheet.Name + "'!$C$3:$C$" + (num + 1).ToString(); oSeries.XValues = "='" + m_Sheet.Name + "'!$A$3:$B$" + (num + 1).ToString(); oSeries.Border.ColorIndex = 45; oSeries.Border.Weight = Excel.XlBorderWeight.xlThick; //oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(2); //oSeries.Border.ColorIndex = 9; //oSeries.Border.Weight = Excel.XlBorderWeight.xlThick; } }}Winform窗体事件：
private void button1_Click_1(object sender, EventArgs e) { string path = ""; OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { path = Path.GetFullPath(ofd.FileName); } ExcelIO exio = new ExcelIO(); DataSet ds = exio.SubImportExcel(path); this.dataGridView1.DataSource = ds.Tables[0]; InsertToSql(ds.Tables[0]); } private void InsertToSql(DataTable dt) { string connString = Properties.Settings.Default.StudentAttendenceConnectionString; SqlParameter[] sqlpars = null; for (int i = 0; i < dt.Rows.Count; i++) { sqlpars = new SqlParameter[6]; sqlpars[0] = new SqlParameter("@StudentNumber", dt.Rows[i].ItemArray[0].ToString()); sqlpars[1] = new SqlParameter("@StudentID", dt.Rows[i].ItemArray[1].ToString()); sqlpars[2] = new SqlParameter("@ClassID",Int32.Parse(dt.Rows[i].ItemArray[2].ToString())); sqlpars[3] = new SqlParameter("@StudentName", dt.Rows[i].ItemArray[3].ToString()); sqlpars[4] = new SqlParameter("@StudentGender", dt.Rows[i].ItemArray[4].ToString()); sqlpars[5] = new SqlParameter("@StudentPhoto", dt.Rows[i].ItemArray[5].ToString()); int row =RunProcedureForUpdate("InsertIntoStudentInfo", sqlpars,connString); if (row == 0) { MessageBox.Show("导入失败"); } else { MessageBox.Show("导入成功"); } } }/// <summary> /// 更新--执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns></returns> private int RunProcedureForUpdate(string storedProcName, IDataParameter[] parameters,string strCon) { using (SqlConnection connection = new SqlConnection(strCon)) { int rowsAffected = 0; try { connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); } catch (Exception ex) { Debug.Print(ex.ToString()); } return rowsAffected; } } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (SqlParameter parameter in parameters) { command.Parameters.Add(parameter); } } return command; }最后如果提示导入成功但未更新到数据库，将Program.cs的main函数中添加
static class Program { /// <summary> /// 应用程序的主入口点。 /// </summary> [STAThread] static void Main() { string dataDir = AppDomain.CurrentDomain.BaseDirectory; if (dataDir.EndsWith(@"\bin\Debug\")|| dataDir.EndsWith(@"\bin\Release\")) { dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName; AppDomain.CurrentDomain.SetData("DataDirectory", dataDir); } Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new MainForm()); } }- Excel2003，2007，2010，2013导入导出sqlserver
- 使用POI导入导出Excel2003、2007示例
- 使用POI导入导出Excel2003、2007示例
- 兼容excel2003以及excel2007导入和导出
- SQLServer导入导出大全
- SQLServer 导入导出
- SqlServer导入导出
- java导入与导出excel，兼容excel2003以及excel2007
- java导入与导出excel，兼容excel2003以及excel2007
- java导入与导出excel，兼容excel2003以及excel2007
- phpexcel 导入excel2003或2007文件数据
- sqlserver导入导出命令大全
- SqlServer大批量导入导出数据
- SQLServer导出导入数据方法
- .Net SqlServer导入导出Excel
- SQLServer BCP 导入导出数据
- JAVA读取Excel2003、2007、2010
- C#导入excel2003数据表
- 划分树小结
- C# 修饰符(modifier)
- 插入mysql乱码问题
- HDU 2108 Shape of HDU
- perl方法积累
- Excel2003，2007，2010，2013导入导出sqlserver
- Poco C++ 日志框架使用 Pcoco Logger
- Argo源码阅读（一）：Jetty
- CStdioFile和CFile对文件异常的处理
- 消除maven的编码字符集告警
- 如何搭建Gerrit+Jenkins(Hudson)测试环境
- Android平台如何使用第三方jar包文件
- Spring中的四种声明式事务的配置
- wfilters小波滤波器
