Excel 200320071202013インポートエクスポートsqlserver
Microsoftを追加Office.Interop.Excelリファレンス.
ExcelIOクラス:
Winformフォームイベント:
最後に、インポートに成功したがデータベースに更新されなかった場合、Program.csのmain関数に追加
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()
{
}
///
///
///
public int ReturnStatus
{
get { return _ReturnStatus; }
}
///
///
///
public string ReturnMessage
{
get { return _ReturnMessage; }
}
///
/// EXCEL DataSet
///
/// Excel
/// DataSet
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;
}
///
/// Excel DataSet
///
///
///
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;
}
}
///
/// DataTable EXCEL
///
///
///
/// Excel
///
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 = " , ！
" + 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;
}
///
/// Excel
///
///
///
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();
}
}
}
///
/// :CreateChart(m_Book, m_Sheet, num);
///
///
///
///
//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(" ");
}
}
}///
/// -- ，
///
///
///
///
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;
}
}
///
/// SqlCommand ( ， )
///
///
///
///
/// SqlCommand
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
{
///
/// 。
///
[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());
}
}