|
Public string RemoveInvalidChar(string
input)
{
List<string> invalidItems = new
List<string>()
{
",",
"/r", System.Environment.NewLine
};
foreach (string invalidItem in
invalidItems)
{
input = input.Replace(invalidItem, "");
}
return
input;
}
private void btnExport_Click(object
sender, EventArgs e)
{
int cols;
//open file
StreamWriter
wr = new System.IO.StreamWriter(@"C:\PerformanceTest.csv", false, Encoding.UTF8);
//Create
datatable having huge rows and columns
DataTable
dt = GetDataTable();
//determine the
number of columns and write columns to file
cols = dt.Columns.Count;
for (int i = 0; i < cols; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + ",");
}
wr.WriteLine();
//write rows to
excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < cols; j++)
{
if
(dt.Rows[i][j] != null)
{
wr.Write(RemoveInvalidChar(dt.Rows[i][j].ToString())
+ ",");
}
else
{
wr.Write(",");
}
}
wr.WriteLine();
}
//close file
wr.Close();
}
}
|
|
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\contact.xls;',
'SELECT *
FROM [Sheet1$]')
SELECT TOP 5 doc_id,doc_name FROM document
|
|
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;Database=C:\excel-sql-server.xls',
[Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data
Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]
|
|
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\contact.xls;',
'SELECT *
FROM [Sheet1$]')
SELECT TOP 5 doc_id,doc_name FROM document
|
|
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
12.0;Database=C:\excel-sql-server.xlsx',
[Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data
Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
|
|
#1: Manually register
those DLLs.
try typing each of
these e.g.
cd c:\windows\sysWOW64 (if it says The system cannot find the path specified, try the next one) cd c:\windows\system32 cd c:\winnt\system32 When one of those doesn't cause an error, stop, you've found the correct folder
7. Now you need to
register the OLE DB 4.0 DLLs by typing these commands and pressing return
after each
#2: Open the project in Visual Studio then:
|
|
CREATE TYPE EmployeeIDType AS
TABLE
(
ID
Int
)
GO
|
