#region Copyright 2013, Andreas Hoffmann
// project location ==> http://datafromfile.codeplex.com/
#region License
/*
New BSD License (BSD)
Copyright (c) 2013, Andreas Hoffmann
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following
conditions are met:
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer
in the documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT
SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
#endregion
#endregion
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
namespace Ipxxl.Data
{
/// <summary>
///
/// </summary>
public sealed class DataFromFile
{
/// <summary>
/// This function returns a DataTable for an Excel-file and the given parameters
/// </summary>
/// <param name="fileName">Name of file to be loaded (e.g. "sample.xls" or "sample.xlsx")</param>
/// <param name="hasHeaders">Headers in first row of Excel sheet?</param>
/// <returns>System.Data.DataTable</returns>
public static DataTable GetDataTableFromExcel(string fileName, bool hasHeaders)
{
if (File.Exists(fileName))
{
var hdr = hasHeaders ? "Yes" : "No";
var strConn = string.Empty;
var extension = fileName.Substring(fileName.LastIndexOf('.')).ToLower();
switch (extension)
{
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
";Extended Properties=\"Excel 12.0 Macro;HDR=" + hdr + ";IMEX=1\"";
break;
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName +
";Extended Properties=\"Excel 8.0;HDR=" + hdr +
";IMEX=1\"";
break;
}
var output = new DataSet();
using (var conn = new OleDbConnection(strConn))
{
conn.Open();
var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
if (schemaTable != null)
foreach (
var tableName in
schemaTable.Rows.Cast<DataRow>()
.Select(schemaRow => schemaRow["TABLE_NAME"].ToString())
.Where(
tableName =>
tableName.LastIndexOf("'", StringComparison.Ordinal) >=
tableName.Length - 1)
)
{
try
{
var cmd = new OleDbCommand("SELECT * FROM [" + tableName + "]", conn)
{
CommandType = CommandType.Text
};
new OleDbDataAdapter(cmd).Fill(output,
tableName.Replace("$", string.Empty)
.Replace("'", string.Empty));
}
catch (Exception ex)
{
throw new Exception(
ex.Message + "Sheet:" + tableName.Replace("$", string.Empty) + ".File:" + fileName, ex);
}
}
conn.Close();
}
return output.Tables[0];
}
throw new FileNotFoundException();
}
/// <summary>
/// This function returns a DataTable for a CSV-file and the given parameters
/// </summary>
/// <param name="fileName">Name of file to be loaded (e.g. "sample.csv")</param>
/// <param name="delimiter">The delimiter char by which the fields are separated (e.g. ";")</param>
/// <param name="fileEncoding">The file encoding</param>
/// <param name="hasHeaders">Headers in first line of file?</param>
/// <returns>System.Data.DataTable</returns>
public static DataTable GetDataTableFromCsv(string fileName, char delimiter, Encoding fileEncoding,
bool hasHeaders)
{
var table = new DataTable();
if (File.Exists(fileName))
{
using (var reader = new StreamReader(fileName, fileEncoding))
{
var rowCounter = 0;
while (true)
{
var line = reader.ReadLine();
if (line == null)
{
break;
}
if (line.Substring(line.Length - 1, 1).Equals(delimiter.ToString()))
{
line = line.Substring(0, line.Length - 1);
}
var parts = line.Split(delimiter);
if (rowCounter == 0 && hasHeaders)
{
table = CreateDataTableHeaders(parts);
}
else
{
table.Rows.Add(parts);
}
rowCounter++;
}
}
return table;
}
throw new FileNotFoundException();
}
/// <summary>
/// This function returns a DataTable for a XML-file
/// </summary>
/// <param name="fileName">Name of file to be loaded (e.g. "sample.xml")</param>
/// <returns>System.Data.DataTable</returns>
public static DataTable GetDataTableFromXml(string fileName)
{
var table = new DataTable();
if (File.Exists(fileName))
{
table.ReadXml(fileName);
return table;
}
throw new FileNotFoundException();
}
/// <summary>
/// Writes the content of the given DataTable to a Xml-file
/// </summary>
/// <param name="dataTable">DataTable with content to write</param>
/// <param name="fileName">Filename for output file</param>
public static void WriteDataTableToXml(DataTable dataTable, string fileName)
{
dataTable.WriteXml(fileName, XmlWriteMode.IgnoreSchema);
}
/// <summary>
/// Writes the content from the given DataTable to a CSV-File
/// </summary>
/// <param name="table">DataTable with Content to write</param>
/// <param name="fileName">Filename for output file</param>
/// <param name="encoding">Encoding of output file</param>
/// <param name="delimiter">A single char which delimits the fields</param>
/// <param name="withHeaders">true => the first row in output file will contain the ColumnNames from given DataTable</param>
public static void WriteDataTableToCsv(DataTable table, string fileName, Encoding encoding, char delimiter, bool withHeaders)
{
using (var writer = new StreamWriter(fileName, false, encoding))
{
if (withHeaders)
{
var headers = new StringBuilder();
foreach (DataColumn column in table.Columns)
{
headers.Append(column.ColumnName);
headers.Append(delimiter);
}
writer.WriteLine(headers.ToString());
}
foreach (DataRow row in table.Rows)
{
var rowdata = new StringBuilder();
foreach (var o in row.ItemArray)
{
rowdata.Append(o);
rowdata.Append(delimiter);
}
writer.WriteLine(rowdata.ToString());
}
}
}
private static DataTable CreateDataTableHeaders(IEnumerable<string> parts)
{
var table = new DataTable();
foreach (var part in parts.Where(part => !string.IsNullOrEmpty(part)))
{
table.Columns.Add(part.Trim());
}
return table;
}
/// <summary>
/// Writes the content from the given DataTable to a Excel-File (OpenXML-Format "xlsx")
/// </summary>
/// <param name="table">DataTable with Content to write</param>
/// <param name="fileName">Filename for output file</param>
/// <param name="withHeaders">true => the first row in output file will contain the ColumnNames from given DataTable</param>
public static void WriteDataTableToExcel2007(DataTable table, string fileName, bool withHeaders)
{
ExcelDocument doc = new ExcelDocument();
doc.CreatePackage(fileName);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
if (withHeaders)
{
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32) 1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(table.Columns.IndexOf(column) + 1, 1, column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
}
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private static Row createContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
private static string getColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend - 1) % 26;
columnName =
Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend - modifier) / 26);
}
return columnName;
}
}
}
