public void GenerateSheets()
{
Microsoft.Office.Interop.Excel.Application oExcelApp = new Microsoft.Office.Interop.Excel.Application();
object readOnly = false;
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook oExcelWorkBook = oExcelApp.Workbooks.Open(lblDisplayFileLocation.Text.ToString().Trim()
//,missing, readOnly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing
);
int numSheets = oExcelWorkBook.Sheets.Count;
excelSheetRecords.FileName = label2.Text.ToString().Trim(); //get the path of the file
excelSheetRecords.FileExt = Path.GetExtension(label2.Text.ToString().Trim()); //get the file extension
foreach (Worksheet worksheet in oExcelWorkBook.Worksheets)
{
Range excelRange = worksheet.UsedRange;
excelSheetRecords.SheetName = worksheet.Name;
int RowCount = excelRange.Rows.Count;
int ColumnCount = excelRange.Columns.Count;
if (excelSheetRecords.FileExt.CompareTo(".xls") == 0 || excelSheetRecords.FileExt.CompareTo(".xlsx") == 0)
{
try
{
System.Data.DataTable dtExcelSheetData = new System.Data.DataTable();
dtExcelSheetData = excelSheetRecords.ReadExcel();
gridMatricsReport.Visible = true;
gridMatricsReport.DataSource = dtExcelSheetData;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else
{
MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
}
}
/*
* foreach (Worksheet worksheet in oExcelWorkBook.Worksheets)
* {
* Range excelRange = worksheet.UsedRange;
* string ExcelSheetName = worksheet.Name;
* int RowCount = excelRange.Rows.Count;
* int ColumnCount = excelRange.Columns.Count;
* for (int r = 1; r <= RowCount; r++)
* {
* for (int c = 1; c <= ColumnCount; c++)
* {
* dynamic cell = excelRange.Cells[r, c];
* try
* {
* if (cell.Locked == false)
* {
* string content = cell.Value2;
* if (content != null && !content.Trim().Equals(""))
* {
* content = content.Trim();
* cell.Value2 = cell.Value2 + " - This is a test";
* }
* }
* }
* catch (Exception)
* {
* // we are using dynamic type for cell variable so
* // the variable might not have all the properties we used in our code
* }
*
* }
* }
* }
*/
//oExcelWorkBook.Save();
//oExcelApp.Application.Quit();
}
/// <summary>
/// Convert Excel- Sheet 1
/// </summary>
/// <param name="excelSheetRecords"></param>
/// <returns></returns>
public bool Save(ExcelSheetRecords excelSheetRecords)
{
try
{
DataTable data = new DataTable();
data.Columns.Add(AccountNumber, typeof(string));
data.Columns.Add(TicketNumber, typeof(string));
data.Columns.Add(TicketAssignedTo, typeof(string));
data.Columns.Add(CreateDateTime, typeof(string));
data.Columns.Add(CompleteDateTime, typeof(string));
data.Columns.Add(WorkTypeName, typeof(string));
data.Columns.Add(NumberOfWorkEntries, typeof(string));
data.Columns.Add(IssueType, typeof(string));
data.Columns.Add(SubIssueType, typeof(string));
data.Columns.Add(Source, typeof(string));
data.Columns.Add(UDFDeferredForResolution, typeof(string));
data.Columns.Add(Priority, typeof(string));
data.Columns.Add(TicketStatus, typeof(string));
data.Columns.Add(TotalWorkedHours, typeof(string));
data.Columns.Add(TicketDurationDays, typeof(string));
data.Columns.Add(TicketDurationHours, typeof(string));
data.Columns.Add(TicketQueue, typeof(string));
DataTable dtData = excelSheetRecords.ReadExcel();
dtData.Columns["F1"].ColumnName = AccountNumber;
dtData.Columns["F2"].ColumnName = TicketNumber;
dtData.Columns["F3"].ColumnName = TicketAssignedTo;
dtData.Columns["F4"].ColumnName = CreateDateTime;
dtData.Columns["F5"].ColumnName = CompleteDateTime;
dtData.Columns["F6"].ColumnName = WorkTypeName;
dtData.Columns["F7"].ColumnName = NumberOfWorkEntries;
dtData.Columns["F8"].ColumnName = IssueType;
dtData.Columns["F9"].ColumnName = SubIssueType;
dtData.Columns["F10"].ColumnName = Source;
dtData.Columns["F11"].ColumnName = UDFDeferredForResolution;
dtData.Columns["F12"].ColumnName = Priority;
dtData.Columns["F13"].ColumnName = TicketStatus;
dtData.Columns["F14"].ColumnName = TotalWorkedHours;
dtData.Columns["F15"].ColumnName = TicketDurationDays;
dtData.Columns["F16"].ColumnName = TicketDurationHours;
dtData.Columns["F17"].ColumnName = TicketQueue;
//remove empty row, where whole columns (row) contains null in excel document
dtData = dtData.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is DBNull || string.IsNullOrWhiteSpace(field as string))).CopyToDataTable();
//We should merge datatype as string for bulk upload
//Other datatypes to string
foreach (DataRow iData in dtData.Rows)
{
data.Rows.Add(iData[AccountNumber], iData[TicketNumber], iData[TicketAssignedTo], iData[CreateDateTime],
iData[CompleteDateTime], iData[WorkTypeName], iData[NumberOfWorkEntries], iData[IssueType],
iData[SubIssueType], iData[Source], iData[UDFDeferredForResolution], iData[Priority],
iData[TicketStatus], iData[TotalWorkedHours], iData[TicketDurationDays], iData[TicketDurationHours],
iData[TicketQueue]);
}
string InsertQuery = "INSERT INTO AllTicketsCreated " +
"(" + AccountNumber + ", " + TicketNumber + ", " + TicketAssignedTo + "," +
CreateDateTime + ", " + CompleteDateTime + ", " + WorkTypeName + "," +
NumberOfWorkEntries + ", " + IssueType + ", " + SubIssueType + "," +
Source + ", " + UDFDeferredForResolution + ", " + Priority + "," +
TicketStatus + ", " + TotalWorkedHours + ", " + TicketDurationDays + "," +
TicketDurationHours + ", " + TicketQueue + ")" +
" VALUES (@" + AccountNumber + " , @" + TicketNumber + ", @" + TicketAssignedTo +
", @" + CreateDateTime + " , @" + CompleteDateTime + ", @" + WorkTypeName +
", @" + NumberOfWorkEntries + " , @" + IssueType + ", @" + SubIssueType +
", @" + Source + " , @" + UDFDeferredForResolution + ", @" + Priority +
", @" + TicketStatus + " , @" + TotalWorkedHours + ", @" + TicketDurationDays +
", @" + TicketDurationHours + " , @" + TicketQueue + " )";
using (var connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + _dbConnection.ConnectionString() + ""))
{
connection.Open();
var adapter = new OleDbDataAdapter();
adapter.InsertCommand = new OleDbCommand(InsertQuery, connection);
adapter.InsertCommand.Parameters.Add("@" + AccountNumber, OleDbType.VarChar, 255, AccountNumber);
adapter.InsertCommand.Parameters.Add("@" + TicketNumber, OleDbType.VarChar, 255, TicketNumber);
adapter.InsertCommand.Parameters.Add("@" + TicketAssignedTo, OleDbType.VarChar, 255, TicketAssignedTo);
adapter.InsertCommand.Parameters.Add("@" + CreateDateTime, OleDbType.VarChar, 25, CreateDateTime);
adapter.InsertCommand.Parameters.Add("@" + CompleteDateTime, OleDbType.VarChar, 25, CompleteDateTime);
adapter.InsertCommand.Parameters.Add("@" + WorkTypeName, OleDbType.VarChar, 255, WorkTypeName);
adapter.InsertCommand.Parameters.Add("@" + NumberOfWorkEntries, OleDbType.VarChar, 10, NumberOfWorkEntries);
adapter.InsertCommand.Parameters.Add("@" + IssueType, OleDbType.VarChar, 100, IssueType);
adapter.InsertCommand.Parameters.Add("@" + SubIssueType, OleDbType.VarChar, 255, SubIssueType);
adapter.InsertCommand.Parameters.Add("@" + Source, OleDbType.VarChar, 100, Source);
adapter.InsertCommand.Parameters.Add("@" + UDFDeferredForResolution, OleDbType.VarChar, 10, UDFDeferredForResolution);
adapter.InsertCommand.Parameters.Add("@" + Priority, OleDbType.VarChar, 100, Priority);
adapter.InsertCommand.Parameters.Add("@" + TicketStatus, OleDbType.VarChar, 100, TicketStatus);
adapter.InsertCommand.Parameters.Add("@" + TotalWorkedHours, OleDbType.VarChar, 10, TotalWorkedHours);
adapter.InsertCommand.Parameters.Add("@" + TicketDurationDays, OleDbType.VarChar, 10, TicketDurationDays);
adapter.InsertCommand.Parameters.Add("@" + TicketDurationHours, OleDbType.VarChar, 10, TicketDurationHours);
adapter.InsertCommand.Parameters.Add("@" + TicketQueue, OleDbType.VarChar, 100, TicketQueue);
//Bulk upload
adapter.Update(data);
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}