using System;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Collections;
using OfficeOpenXml.Drawing;
using OfficeOpenXml;
using System.Threading;
using System.Windows.Threading;
class Log_Excel
{
public string FLD1 { get; set; }
public string FLD2 { get; set; }
public string FLD3 { get; set; }
public Log_Excel(string in_fld1, string in_fld2, string in_fld3)
{
this.FLD1 = in_fld1;
this.FLD2 = in_fld2;
this.FLD3 = in_fld3;
}
}
Microsoft.Win32.OpenFileDialog ofd = new Microsoft.Win32.OpenFileDialog();
string CStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
DataTable DtTbl_Excel = new DataTable();
خواندن از Excel و ریختن در DataGrid
private void ReadFromExcel ()
{
ofd.Filter = “Excel Files(*.XLSX;*.XLS;|*.XLSX;*.XLS;” + “|All Files (*.*)|*.*”;
ofd.Multiselect = false;
bool showdgl = ofd.ShowDialog().Value;
if (showdgl == true)
{
string strConn = “Provider=Microsoft.Jet.OLEDB.12.0; Data Source = “ + ofd.FileName.Trim() + “ ; Extended Properties=\”Excel 12.0; HDR = YES ;\””;
string filePath = ofd.FileName.Trim();
string sheetName = “Sheet1”;
using (ExcelPackage excelPkg = new ExcelPackage())
using (FileStream stream = new FileStream(filePath, FileMode.Open))
{
excelPkg.Load(stream);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets[sheetName];
DtTbl_Excel = WorksheetToDataTable(oSheet);
dataGrid1.ItemsSource = WorksheetToDataTable(oSheet).DefaultView as DataView;
}
}
}
تبدیل داده خوانده شده از Excel به DataTable
private DataTable WorksheetToDataTable(ExcelWorksheet oSheet)
{
int totalRows = oSheet.Dimension.End.Row;
int totalCols = oSheet.Dimension.End.Column;
DataTable dt = new DataTable(oSheet.Name);
DataRow dr = null;
try
{
for (int i = 1; i <= totalRows; i++)
{
if (i > 1) dr = dt.Rows.Add();
for (int j = 1; j <= totalCols; j++)
{
if (i == 1)
dt.Columns.Add(oSheet.Cells[i, j].Value.ToString().Trim());
else
dr[j - 1] = oSheet.Cells[i, j].Value.ToString().Trim();
}
}
}
catch (Exception)
{
MessageBox.show("خطای کلیدی !!! نام ستون تکرای و جود دارد و یا تعداد مشخـصـه غلط است");
}
return dt;
}
نوشتن داده خوانده شده از Excel به Table
private void WritetoTable()
{
SqlConnection _Conn = new SqlConnection(CStr);
// Open the Database Connection
_Conn.Open();
DataRow DtRow;
for (int i = 0; i < DtTbl_Excel.Rows.Count; i++)
{
DtRow = DtTbl_Excel.Rows[i];
string key_Str;
try
{
key_Str = DtRow[1].ToString().Trim() + Date_Class.persianDate_To_Number(DtRow[0].ToString().Trim());
string _Insert = @"insert into Tbl_Info_Log
( Cod_Bus_Date , Date_Log , Cod_BUS, Tarakonesh)
Values( " + key_Str + " , '" + Date_Class.Number_To_persianDate(Date_Class.persia nDate_To_Number(DtRow[0].ToString().Trim())) + "' , " + Clock_Class.Null_To_Zero(DtRow[1].ToString().Trim()) + " , " + Clock_Class.Null_To_Zero(DtRow[2].ToString().Trim()) + " )";
// Initialize the command query and connection
SqlCommand _cmd = new SqlCommand(_Insert, _Conn);
// Execute the command
_cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
}
{
پاسخ با نقل قول
نوشته شده توسط behnam-soft
