I have an asp.net website that will generate some excel files with 7-8 sheets of data.The best solution so far seems to be NPOI, this can create excel files without installing excel on the server, and has a nice API simillar to the excel interopHowever i can't find a way to dump an entire datatable in excel similar to CopyFromRecordset
View 1 RepliesI want export datatable to dbf . the following is my code. the conection dont open
SqlConnection cn = new
SqlConnection("Data
Source=(local);Initial Catalog=test;Integrated Security=True");
SqlDataAdapte da =
new
SqlDataAdapter("select
* from test", cn);
DataSet ds = new
DataSet();
da.Fill(ds, "test");
string directory =
"E://";
OleDbConnection connection =
new
OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=E:\TEST.DBF;");
connection.Open();
OleDbCommand command = connection.CreateCommand();
if (System.IO.File.Exists(directory
+ @"TEST.DBF"))
command.CommandText =
"DELETE FROM Test
else
command.CommandText =
"CREATE TABLE Test (ID int, name varchar(50),last_name varchar(50))";
command.ExecuteNonQuery();
command.CommandText =
"SELECT * FROM Test";
OleDbDataAdapter adapter =
new
OleDbDataAdapter(command);
OleDbCommand insertCommand = connection.CreateCommand();
insertCommand.CommandText = "INSERT INTO Test (ID, name,last_name) VALUES (pID,pname,plast_name)";
}
foreach (DataRow
dr in ds.Tables["test"].Rows)
{
int id =
int.Parse(dr["ID"].ToString());
string name = dr["name"].ToString();
string last_name = dr["last_name"].ToString();
insertCommand.Parameters.Add("pID",
OleDbType.Integer, 4, ID);
insertCommand.Parameters.Add("pname",
OleDbType.VarChar, 50, name);
insertCommand.Parameters.Add("plast_name",
OleDbType.VarChar, 50, last_name);
insertCommand.UpdatedRowSource =
UpdateRowSource.None;
i have datatable with data i want to export or wite this data to excel file
i have some thousands of rows in datatable
but i want not use Response.Output.Write
Exporting DataTable data to CSV file. It is working fine as expected as long as the export records are less than 65,536. When the records are more than 65,536 , only 65,536 records are shown in CSV file (remaing records are not shown). When the export is done(with more than 65,536 records), opening the file shows below message window. File is not loaded completely. When the Ok button is selected, below text displayed...
This message can appear if: You are trying to open a file that contains more than 65,536 rows or 256 columns. To fix this problem, open the source file in a text editor such as Microsoft Word. Save the source file as several smaller files that conform to this row and column limit, and then open the smaller files in Excel. If the source data cannot be opened in a text editor, try importing the data into Microsoft Access, and then exporting subsets of the data from Access to Excel. You are trying to paste tab-delimited data into an area that is too small. To fix this problem, select an area in the worksheet large enough to accommodate every delimited item.
Notes: You can not configure Excel to exceed the limit of 65,536 rows and 256 columns. By default, Excel places three worksheets in a workbook file. Each worksheet can contain 65,536 rows and 256 columns of data, and workbooks can contain more than three worksheets if your computer has enough memory to support the additional data. Solution:- Seems like here is the suggested solution. But it takes ages to export and show data. An export is done about 15mins ago, still it is doing that. Has anyone managed to find/resolve this issue?
[Code]....
how to export a datatable to excel with the datatable column name to the heading of the excel sheet.
View 1 RepliesI have a DataTable which contains a result of a query. The data are name, address, city and province.
I need to write these data into a txt file, but I must use three columns, so the output should be like this:
Name1 Name2 Name3
Address1 Address2 Address3
City1 Provvince1 City2 Provvince2 City3 Provvince3
Name4 Name5 Name6
Address4 Address5 Address6
City4 Provvince4 City5 Provvince5 City6 Provvince6
The problem is that when I cycle through the rows of DataTable I can't format the text file as I wish.
How can Export dataTable to Exel
View 5 Repliesi said in the title i found the NPOI API witch can generate word documents without need of MS Office installed.
My problem is i can't find yet a complete tutorial to create a word reeport based on template.
i have a datatable to bind the gridview in my page. now i need to have a export button in near gridview, if we click the button then the gridview values (datatable) to excel.
View 3 RepliesI want to get print out for Dataset which creates a pdf file as out put.
View 1 Replies When i export the data table to excel 2003 i get the output in a very fast manner and when i want to use the same in excel 2007 debugging gets stopped and nothing happens.... It does not show any errror on it to. When i slowly debugged i find that in saveas() it takes longer time and it seems like execution gets stopped in this process.
Its like in new window it shows loading for more than 10 min and after some time the particular window gets closed and nothing happens can anyone tell me what can be wrong.When the same code works great when i export to 2003.
When I am Exporting CSV Using Reader,Here Attached Coding for your reference,
PrecisionColumns = New String() {"Salequantity", "Salevalue", "Purchasequantity", "Purchasevalue", "Stockvalue"}
If Trim(Rdr.GetValue(I).ToString() <> "") Then
csv += Format(CDbl(Rdr.GetValue(I).ToString()), "0.00").Replace(",", ";") + ","c
End If
csv += vbCr & vbLf
Response.Write(csv)
... ...
I get precision in CSV file ,but when i open the csv file in excel Precision not showing, how to show precision on excel sheet...
I have 25 collumn, i want export 20 collumn to sheet 1 and 5 collumn to sheet 2.
View 1 RepliesI am using the below code to Export DataTable to Word,Excel,CSV format & it's working fine. But problem is that this code export to MS Word 2003,Excel 2003 & CSV version. I need to Export my DataTable to MS Word 2007,Excel 2007 & CSV because I am supposed to handle more than 100,000 records at a time and as we know Excel 2003 supports for only 65,000 records.
how to export DataTable or DataSet to MS Word 2007,Excel 2007 & CSV.
[Code]....
I am trying to create double and number format cells in excel using NPOI library. I used code like
Dim cell As HSSFCell = row.CreateCell(j)
cell.SetCellValue(Double.Parse(dr(col).ToString))
In excel numbers are aligning right but when I check format it is showing in "General"
then I changed my code to like below
Dim cell As HSSFCell = row.CreateCell(j)
cell.SetCellValue(Double.Parse(dr(col).ToString))
Dim cellStyle As HSSFCellStyle = hssfworkbook.CreateCellStyle
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,#0.0")
cell.CellStyle = cellStyle
Then While opening file it is giving error and also taking so long to open. But Excel format showing in "Number" error showing is like below.
I am using the below code to Export DataTable to MS Word,Excel,CSV format & it's working fine. But problem is that this code export to MS Word 2003,Excel 2003 version. I need to Export my DataTable to Word 2007,Excel 2007,CSV because I am supposed to handle more than 100,000 records at a time and as we know Excel 2003 supports for only 65,000 records.
how to export DataTable or DataSet to MS Word 2007,Excel 2007.
[Code]....
'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Word.DataTable'
View 3 Repliesi have in grid view that grid view have very long string. when i export grid view export in excel it that string shows in single line
View 1 RepliesI have a web form that contains a GridView, which impliments Search in Grid with a TextBox and Button control in the footer. This works as I have it now. I have also implimented a Export to Excel function. The Export to Excel works by itself if the Search in Grid function is not included in the footer.The Export to Excel function errors when trying to render the Grid for the export. Is there a better way to incorporate both functions and accomplish the same thing? Am I missing something simple?
[Code]...
I was reading: [URL]
But I have give a compilation error:
CS0246: The type or namespace name 'DataTable' could not be found (are you missing a using directive or an assembly reference?
i have the dataset with one table.Table contains three column like 'Name','Location','Pin'.I would like to move the data from another table based on schema.
View 2 RepliesIf I am passed a datatable and I cant change the column structure..is there anyway to set an existing column to a Primary key so I can easily Find() the row I am looking for?
View 1 Repliesim trying to copying specific record from one datatable to another datatable i used below code
public System.Data.DataTable selectspecificdatarow(System.Data.DataTable dtg, int count, int startindex)
{
System.Data.DataTable dtn = dtg.Clone();
for (int i =startindex; i < count; i++)
[Code]....
its taking too long time in cloneing is there any better way to do this task which is Time effecent
how to join two datatable datas into one datatable to show in one gridview
i.e in 1 datatable i have username and pwd and in another datatable i have that user details. how to show all these in one datatable to get those values display in gridview(asp.net)
Dim dt As New DataTable Dim da As New SqlDataAdapter(s, c)
c.Open()
if Not IsNothing(da) Then
da.Fill(dt)
[code]...
When I call da.fill I am inserting all records from my query. I was then hoping to filter them to display only those where the GroupingID is equal to 0. When I run the above code. I am presented with all the data, the filter did not work. Please can you tell me how to get this working correctly.