// DataReader
public static void ExportToCSV(string iConnectionString, string iQuery, string iExcelFilePath = null)
{
try
{
if (string.IsNullOrEmpty(iConnectionString))
{
throw new Exception("Null or empty input table!");
}
// Default file name
if (string.IsNullOrEmpty(iExcelFilePath))
{
iExcelFilePath = DateTime.UtcNow.ToString("yyyy-MM-dd") + "_[" + Guid.NewGuid() + "]" +
_FILEEXTENSIONS;
}
// Check file format - extensions equals .csv
if (!iExcelFilePath.EndsWith(_FILEEXTENSIONS, StringComparison.InvariantCultureIgnoreCase))
{
throw new Exception("Specified file is not a valid format!");
}
// check folder path
if (iExcelFilePath.IndexOf('\\') >= 0)
{
string mParentFolder = iExcelFilePath.Substring(0, iExcelFilePath.LastIndexOf('\\')) + "\\";
if (!Directory.Exists(mParentFolder))
{
throw new Exception("Specified folder does not exist!");
}
}
else
{
iExcelFilePath = Path.Combine(Environment.CurrentDirectory, iExcelFilePath);
}
// Create file before adding data
if (!File.Exists(iExcelFilePath))
{
File.Create(iExcelFilePath).Close();
}
SqlConnection mConnection = null;
SqlDataReader mDataReader = null;
try
{
mConnection = new SqlConnection(iConnectionString);
mDataReader = MsSql.GetDataReader(iQuery, ref mConnection);
StringBuilder mAppendText = new StringBuilder();
// column headings
for (int i = 0; i < mDataReader.FieldCount; i++)
{
mAppendText.Append(mDataReader.GetName(i) + _DELIMITER);
}
File.AppendAllText(iExcelFilePath, mAppendText.ToString());
int mRowIndex = 1;
if (mDataReader.HasRows)
{
while (mDataReader.Read())
{
mRowIndex++;
mAppendText = new StringBuilder("\n");
for (int i = 0; i < mDataReader.FieldCount; i++)
{
mAppendText.Append(jAdapter.ToString(mDataReader[i]) + _DELIMITER);
}
File.AppendAllText(iExcelFilePath, mAppendText.ToString());
if (TableRowExported != null)
{
TableRowExported(mRowIndex);
}
}
}
}
catch
{
}
finally
{
if (mDataReader != null)
{
mDataReader.Close();
}
if (mConnection != null)
{
mConnection.Close();
mConnection.Dispose();
}
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: " + ex.Message);
}
}
// DataReader
public static string ExportToExcel(string iConnectionString, string iQuery, string iExcelFilePath = null)
{
try
{
// load excel, and create a new workbook
Excel.Application mExcelApp = new Excel.Application();
mExcelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet mWorkSheet = mExcelApp.ActiveSheet;
SqlConnection mConnection = null;
SqlDataReader mDataReader = null;
try
{
mConnection = new SqlConnection(iConnectionString);
mDataReader = MsSql.GetDataReader(iQuery, ref mConnection);
// column headings
for (int i = 0; i < mDataReader.FieldCount; i++)
{
mWorkSheet.Cells[1, (i + 1)] = mDataReader.GetName(i);
}
int mRowIndex = 1;
if (mDataReader.HasRows)
{
while (mDataReader.Read())
{
mRowIndex++;
for (int i = 0; i < mDataReader.FieldCount; i++)
{
mWorkSheet.Cells[mRowIndex, (i + 1)] = jAdapter.ToString(mDataReader[i]);
}
if (TableRowExported != null)
{
TableRowExported(mRowIndex);
}
}
}
}
catch
{
}
finally
{
if (mDataReader != null)
{
mDataReader.Close();
}
if (mConnection != null)
{
mConnection.Close();
mConnection.Dispose();
}
}
// check fielpath
if (!string.IsNullOrEmpty(iExcelFilePath))
{
try
{
var mExcelFilename = CheckFilename(iExcelFilePath);
mWorkSheet.SaveAs(mExcelFilename);
mExcelApp.Quit();
return(mExcelFilename);
}
catch (Exception ex)
{
throw new Exception("Excel file could not be saved! " + ex.Message);
}
}
else // no filepath is given
{
mExcelApp.Visible = true;
return(string.Empty);
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: " + ex.Message);
}
}
// DataReader
public static string ExportToCsv(string iConnectionString, string iQuery, string iCsvFilePath = null,
bool iOverwrite = true, bool iWithHeader = true)
{
try
{
var mCsvFilename = CheckFilename(iCsvFilePath, iOverwrite);
// Create file before adding data
if (!File.Exists(mCsvFilename))
{
File.Create(mCsvFilename).Close();
}
SqlConnection mConnection = null;
SqlDataReader mDataReader = null;
try
{
mConnection = new SqlConnection(iConnectionString);
mDataReader = MsSql.GetDataReader(iQuery, ref mConnection);
var mAppendText = new StringBuilder();
if (iWithHeader)
{
// column headings
for (int i = 0; i < mDataReader.FieldCount; i++)
{
mAppendText.Append(mDataReader.GetName(i) + Delimiter);
}
File.AppendAllText(mCsvFilename, mAppendText.ToString());
}
int mRowIndex = 1;
if (mDataReader.HasRows)
{
while (mDataReader.Read())
{
mRowIndex++;
mAppendText = new StringBuilder("\n");
for (int i = 0; i < mDataReader.FieldCount; i++)
{
mAppendText.Append(jAdapter.ToString(mDataReader[i]) + Delimiter);
}
File.AppendAllText(mCsvFilename, mAppendText.ToString());
if (TableRowExported != null)
{
TableRowExported(mRowIndex);
}
}
}
}
catch
{
}
finally
{
if (mDataReader != null)
{
mDataReader.Close();
}
if (mConnection != null)
{
mConnection.Close();
mConnection.Dispose();
}
}
return(mCsvFilename);
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: " + ex.Message);
}
}