xxxxxxxxxx
// Using OleDb:
private static DataTable ExcelToDataTable(string fileFullPath)
{
try
{
DataTable dt = new DataTable();
string StartingColumn = "A";
string EndingColumn = "D";
string StartReadingFromRow = "1";
string HDR = "YES";
string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection cnn = new OleDbConnection(ConStr);
cnn.Open();
DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname = "";
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))
{
sheetname = drSheet["TABLE_NAME"].ToString();
OleDbCommand oconn = new OleDbCommand("select * from ["
+ sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn);
OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
adp.Fill(dt);
cnn.Close();
}
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
// ------------------------------------------------------
// using openxmlSDK:
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace Core_Excel.Utilities
{
static class ExcelUtility
{
public static DataTable Read(string path)
{
var dt = new DataTable();
using (var ssDoc = SpreadsheetDocument.Open(path, false))
{
var sheets = ssDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
var relationshipId = sheets.First().Id.Value;
var worksheetPart = (WorksheetPart) ssDoc.WorkbookPart.GetPartById(relationshipId);
var workSheet = worksheetPart.Worksheet;
var sheetData = workSheet.GetFirstChild<SheetData>();
var rows = sheetData.Descendants<Row>().ToList();
foreach (var row in rows) //this will also include your header row...
{
var tempRow = dt.NewRow();
var colCount = row.Descendants<Cell>().Count();
foreach (var cell in row.Descendants<Cell>())
{
var index = GetIndex(cell.CellReference);
// Add Columns
for (var i = dt.Columns.Count; i <= index; i++)
dt.Columns.Add();
tempRow[index] = GetCellValue(ssDoc, cell);
}
dt.Rows.Add(tempRow);
}
}
return dt;
}
private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
var stringTablePart = document.WorkbookPart.SharedStringTablePart;
var value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
return stringTablePart.SharedStringTable.ChildElements[int.Parse(value)].InnerText;
return value;
}
public static int GetIndex(string name)
{
if (string.IsNullOrWhiteSpace(name))
return -1;
int index = 0;
foreach (var ch in name)
{
if (char.IsLetter(ch))
{
int value = ch - 'A' + 1;
index = value + index * 26;
}
else
break;
}
return index - 1;
}
}
}
To create a DataTable from an Excel file in C#, you can use the ExcelDataReader and System.Data libraries.
xxxxxxxxxx
using System.Data;
using ExcelDataReader;
using System.IO;
// ...
// Define the path to the Excel file
string filePath = @"C:\path\to\file.xlsx";
// Open the file using a stream
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
// Create the reader object
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// Create the dataset to hold the data
var result = reader.AsDataSet();
// Get the first table (assuming there is only one)
DataTable table = result.Tables[0];
// Do something with the table...
}
}
xxxxxxxxxx
/// <summary>
/// Convert DataTable to Excel File
/// </summary>
/// <param name="filePath">Excel FilePath</param>
/// <param name="table">DataTable</param>
/// <param name="fileName">Excel Filename</param>
/// <returns></returns>
public bool Write(string filePath, DataTable table, string fileName)
{
try
{
bool success = false;
var today = Convert.ToDateTime(DateTime.Now).ToString("yyyyMMddHHmmss");
//delete existing files
// Util_Directory.DeleteFiles(filePath);
var newDir = filePath + "\\" + today;
//if dir not exist create
Util_Directory.CreateDirectory(newDir);
FileInfo file = new FileInfo(Path.Combine(newDir, fileName));
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(file))
{
var workSheet = package.Workbook.Worksheets.Add("Sheet1");
//add column headers
var columns = table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
int i = 1;
foreach (var column in columns)
{
workSheet.Cells[1, i].Value = column;
i++;
}
// add row values
int startRow = 2;
foreach (DataRow row in table.Rows)
{
int startColumn = 1;
foreach (var item in row.ItemArray)
{
workSheet.Cells[startRow, startColumn].Value = item;
startColumn++;
}
startRow++;
}
workSheet.Cells.AutoFitColumns();
package.Save();
success = true;
}
return success;
}
catch (Exception)
{
throw;
}
}
xxxxxxxxxx
using System;
using System.Data;
using Microsoft.Office.Interop.Excel;
namespace ExcelFromDataTableExample
{
class Program
{
static void Main(string[] args)
{
// Create a new DataTable
DataTable dataTable = new DataTable("TestTable");
// Add some columns to the DataTable
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// Add some rows to the DataTable
dataTable.Rows.Add("John Smith", 35);
dataTable.Rows.Add("Jane Doe", 25);
dataTable.Rows.Add("Bob Johnson", 42);
// Create a new Excel application
Application excel = new Application();
// Add a new workbook
Workbook workbook = excel.Workbooks.Add(Type.Missing);
// Get the first worksheet
Worksheet worksheet = (Worksheet)workbook.ActiveSheet;
// Set the worksheet name
worksheet.Name = "TestSheet";
// Write the DataTable to the worksheet
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
worksheet.Cells[i + 1, j + 1] = dataTable.Rows[i][j].ToString();
}
}
// Save the workbook
workbook.SaveAs(@"C:\Temp\TestWorkbook.xlsx");
// Close the workbook and Excel application
workbook.Close();
excel.Quit();
}
}
}
xxxxxxxxxx
string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = default(DataTable);
OleDbCommand oleExcelCommand = default(OleDbCommand);
OleDbDataReader oleExcelReader = default(OleDbDataReader);
OleDbConnection oleExcelConnection = default(OleDbConnection);
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test.xls;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();
dtTablesList = oleExcelConnection.GetSchema("Tables");
if (dtTablesList.Rows.Count > 0)
{
sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
}
dtTablesList.Clear();
dtTablesList.Dispose();
if (!string.IsNullOrEmpty(sSheetName)) {
oleExcelCommand = oleExcelConnection.CreateCommand();
oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
oleExcelCommand.CommandType = CommandType.Text;
oleExcelReader = oleExcelCommand.ExecuteReader();
nOutputRow = 0;
while (oleExcelReader.Read())
{
}
oleExcelReader.Close();
}
oleExcelConnection.Close();