-
Notifications
You must be signed in to change notification settings - Fork 0
/
ExcelUtils.cs
156 lines (141 loc) · 5.43 KB
/
ExcelUtils.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace GINtool
{
class ExcelUtils
{
public enum CVErrEnum : Int32
{
ErrDiv0 = -2146826281,
ErrGettingData = -2146826245,
ErrNA = -2146826246,
ErrName = -2146826259,
ErrNull = -2146826288,
ErrNum = -2146826252,
ErrRef = -2146826265,
ErrValue = -2146826273
}
public static DataTable ReadExcelToDatable(Excel.Application theApp, string worksheetName, string saveAsLocation, int HeaderLine, int ColumnStart)
{
if (theApp != null)
theApp.EnableEvents = false;
else
return null;
DataTable dataTable = new DataTable();
Excel.Application excel;
Excel.Workbook excelworkBook;
Excel.Worksheet excelSheet;
Excel.Range range;
try
{
// Get Application object.
excel = theApp;
excel.ScreenUpdating = false;
//excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Open(saveAsLocation);
excel.ActiveWindow.Visible = false;
// Work sheet
excelSheet = (Excel.Worksheet)excelworkBook.Worksheets.Item[worksheetName];
range = excelSheet.UsedRange;
int cl = range.Columns.Count;
// loop through each row and add values to our sheet
int rowcount = range.Rows.Count; ;
//create the header of table
for (int j = ColumnStart; j <= cl; j++)
{
dataTable.Columns.Add(Convert.ToString
(range.Cells[HeaderLine, j].Value2), typeof(string));
}
//filling the table from excel file
for (int i = HeaderLine + 1; i <= rowcount; i++)
{
DataRow dr = dataTable.NewRow();
for (int j = ColumnStart; j <= cl; j++)
{
dr[j - ColumnStart] = Convert.ToString(range.Cells[i, j].Value2);
}
dataTable.Rows.InsertAt(dr, dataTable.Rows.Count + 1);
}
//now close the workbook and make the function return the data table
excel.ScreenUpdating = true;
excelworkBook.Activate();
if (excel.ActiveWindow != null)
excel.ActiveWindow.Visible = true;
excelworkBook.Close();
theApp.EnableEvents = true;
theApp.Visible = true;
excelSheet = null;
range = null;
excelworkBook = null;
return dataTable;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
finally
{
theApp.EnableEvents = true;
}
}
public static string[] ReadExcelToDatableHeader(Excel.Application theApp, string worksheetName, string saveAsLocation, int HeaderLine, int ColumnStart)
{
if (theApp != null)
theApp.EnableEvents = false;
else
return null;
DataTable dataTable = new DataTable();
Excel.Application excel;
Excel.Workbook excelworkBook;
Excel.Worksheet excelSheet;
Excel.Range range;
try
{
// Get Application object.
excel = theApp;
excel.ScreenUpdating = false;
//excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Open(saveAsLocation);
excel.ActiveWindow.Visible = false;
// Work sheet
excelSheet = (Excel.Worksheet)excelworkBook.Worksheets.Item[worksheetName];
range = excelSheet.UsedRange;
int cl = range.Columns.Count;
List<string> list = new List<string>();
for (int j = ColumnStart; j <= cl; j++)
{
list.Add(Convert.ToString(range.Cells[HeaderLine, j].Value2));
}
//now close the workbook and make the function return the data table
excel.ScreenUpdating = true;
excelworkBook.Activate();
if (excel.ActiveWindow != null)
excel.ActiveWindow.Visible = true;
excelworkBook.Close();
theApp.EnableEvents = true;
theApp.Visible = true;
excelSheet = null;
range = null;
excelworkBook = null;
return list.ToArray();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
finally
{
theApp.EnableEvents = true;
}
}
}
}