In my ASP.NET application, I need to write DataTable as CSV response to the customer. Everything is working fine except a column which has a numbers.
Ex: 7002136138603600000
But when I open the CSV in Excel, it is showing in exponential format.
Something like this: 7E+18
what i needs to be done in order to show these values as test instead in exponential format?
I have a excel xlsx workbook that needs to be loaded into various SQL tables after the file's data has gone though various business logic checks. There can be multiple excel files as input to the program so i'm using the standard connection string and a data adapter to append the entire sheet into a DataTable so all the data can be checked at the same time. The code below works for this (sort of).
excelCon = new OleexcelCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties="Excel 12.0;HDR=No;IMEX=1";");
excelCon.Open();
strExcelSheetNames = GetSheetNames(excelCon);
for (int x = 0; x < strExcelSheetNames.Length; x++)
{
excelCmd.CommandText = "SELECT * FROM [" + strExcelSheetNames[x] + "]";
excelCmd.CommandType = CommandType.Text;
excelCmd.Connection = excelCon;
excelAdapter = new OleDbDataAdapter(excelCmd);
excelAdapter.Fill(dtExcelConvert);
}
I'm having an issue with the number columns however, if the number in the xlsx file is 612561120001 and it's displayed as 6.12561E+11 when i read it from the data table and convert it to a number it appears as 612561120000. I have tried to convert the file to a csv file and read it in but they're comma's in a lot of the fields which cause issues when splitting the data.
When data comes more than 30 Rows in Grid then the Excel goes in different format.There is parsian/Urdu Font in Grid data.
<div id="PrintDiv">
<asp:GridView ID="gvExamReport" ClientIDMode="Static" runat="server" OnDataBound="gvExamReport_DataBound"
HeaderStyle-Height="20" HeaderStyle-HorizontalAlign="Center" OnRowDataBound="gvExamReport_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="مرتبة">
[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 have try to export data to excel using gridview.
I have export it successfully but some data is missing which is due to the column format in the exported excel file is not TEXT .
[Code]....
I need to set the exported excel file column format to TEXT but find no solution on this.
I am using the following code to export some csv data to excel. The code has been working fine for ages (using IE) but has recently stopped working. I have noticed that the only way to get the export to work is by holding down the control key?
I have tried a few different contenttypes and headers but always get the same issue whereby I have to hold the control key down before I am asked to save or open the file.
[Code]....
using asp.net 3.5 I am trying to generate a formatted excel spreadsheet and the way i am doing it is:1.Format the excel sheet with data 2.Save it as an xml spreadhseet 3.View it in notepad/textpad and remove the "data" from the xml and have a content holder
such as "DYNAMICDATA" 4.Build dynamic xml for data and replace the "DYNAMICDATA" place holder with this fresh data.5.have all this in a stringbuilderI m stuck here, I want to be able to Response.Write(stringbuilder)but it does not work, I have used the appropriate MIME types etc.However Response.TransmitFile() works when i just transmit an xml spreadsheet to browser.but Response.TransmitFile() takes a file as an argument, I can create a temp file write the created xml and then transmit but that just seems like a heavy overhead.Is there a way i can without creating a file, just transmitXml() using the stringbuilder contents and let the user save the spreadsheet.
I have a webservice that sends the response back in XML format.I'm able to connect and call the webservice adding an external web reference to the Visual Studio project.Then in my code behind:As New servicename_addedLabel1.Text = servicename_added.Functionexposed(param1, param2, etc)With that code I can get the response in a large label and unstructured data. If I "view source" I see the XML structured data.I have tried to create an XML document without success.My goal is to parse the response and write it to a database separating all the fields.
View 5 RepliesI have a simple webservice that I would like to upload a file to. The problem is that I need the response in json.
Form my experience in order to get a response in Json my request has to have a content-type of 'application/json'. But ofcourse this cannot be the case with a file upload since the content type will have to be 'multipart/form-data'.
In my Json i want to return a value showing whether successful and a filename.
[code]...
I'll preface this by stating I am a rank amateur when it comes to web dev.
I have a web appliance that provides XML data when issued a query string such as:
[URL]
I created a simple form that allows users to modify values of this query and have the appropriate XML returned. Here is that form:
<form id= "report" action="https://mysite.com/api/reporting.ns?" name="report">
Username: <input name="username"><br />
Password: <input type="password" name="password"><br />
<input type="hidden" name="generate_report" value="SupportSession">
Start Date: <input name="start_date">
<input type="hidden" name="duration" value="0">
<input type="hidden" name="limit" value="all">
<input type="submit" value="Show Report">
What I have not been able to accomplish is formatting that XML response so it looks pretty.
I have created an XSLT that formats the XML nicely and have used javascript to transform the XML using the XSLT like this:
function loadXMLDoc(dname)
{
if (window.XMLHttpRequest)
{
xhttp=new XMLHttpRequest();
}
else
{
xhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xhttp.open("GET",dname,false);
xhttp.send("");
return xhttp.responseXML;
}
function displayResult()
{
xml=loadXMLDoc("Report.xml");
xsl=loadXMLDoc("Report.xsl");
// code for IE
if (window.ActiveXObject)
{
ex=xml.transformNode(xsl);
document.getElementById("content").innerHTML=ex;
}
// code for Mozilla, Firefox, Opera, etc.
else if (document.implementation && document.implementation.createDocument)
{
xsltProcessor=new XSLTProcessor();
xsltProcessor.importStylesheet(xsl);
resultDocument = xsltProcessor.transformToFragment(xml,document);
document.getElementById("content").appendChild(resultDocument);
}
}
Which works locally but I run up against the cross domain security problem as the web appliance is hardened and I am not able to place any code on it.
I have been searching this site and the web for a couple days looking for a method to accomplish this using other means [asp?] and have been largely unsuccessful. The main reason this is so is that I don't know asp so I'm not sure how to attack it.
I think creating the query string in a form, then loading the resulting xml response from the web appliance to a string and applying the XSLT that resides on the server to it might be an option but haven't been able to make it work; again because I'm not really sure what I'm doing.
Here is my XSLT:
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<div class="contentBox"> -->
[Code].....
Further information:
I found a way in asp to retrieve remote xml that works for URL's like http://site.com/file.xml but doesnt work for my URL. Maybe because it is a query? I've looked at way to encode/encapsulate my URL but haven't found anything that works.
[URL]
I'm using .net 3.5 and am currently creating a web application used to generate a report through Aspose.CellsActually, the page is composed in a form where I get the configuration of the report I have to generate. The "generation" button is in an update panel. When I click on it, the "generation" button is hidden and a progress bar appears. When the excel file is generated, I save it in a memory stream and I send it back to the aspx page where I change the headers to allow the file's download.
View 3 RepliesI am having a great deal of difficulty getting a ModalPopupExtender to hide after I export a datatable to Excel.
If I simply rem out the Response.write in the code below, everything works correctly, but of course I don't get my excel sheet.
I have tried all kinds of ways to do this. Including calling a javascript function to hide it, wrapping the Panel in an updatePanel and placing the TransactionControl in a asp:PostBackTrigger.
All without affect. Anyone have a clue as to how to do this?
I was also hoping some event would fire after the ModalPopupExtender hides, but of course there is no such luck.
[Code]....
how to read image in cell of the excel sheet. This sheet contain .jpeg formated images. I want as they are means in jpeg format. i am using asp.net and c# for this.
View 1 RepliesI'm trying to export my data to an Excel file. Here's my code behind:
[Code]....
And here's the GridView control:
[Code]....
The problem I have is that when I exported into Excel, the texts in column two (which is the Lable1 in the Gridview) does not work when applying Wrap Text in MS Excel 2007. I believed that there is something in the btnExcel_Click method causes the texts to appear as ## all the time regardless if I format the cell to Wrap Text. If I copy the cell and pasted into another sheet or cell as "Paste Values" then the texts showed up and wrap text is working. What could I have changed on the code behind so that it appears as plain texts in Excel?
I've a gridview which I am rendering to a html text writer and allowing clients to download as an excel spreadsheet using Response.Write. I'm using AJAX in my website now and suddenly it stopped working. This is the code I'm using
public static void Convert(DataSet ds, HttpResponse response)
{
try
{
//1. declare filename to export
string filename = "export";
//2. clear response object
response.Clear();
response.Charset = "";
//3. set response mime type to excel
response.ContentType = "application/vnd.ms-excel";
//4. prompt user to download the file
response.AppendHeader("content-disposition", "attachment; filename=" + filename + ".xls");
//5. create a string writer
StringWriter stringWrite = new StringWriter();
//6. create html writer which uses the string writer
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
//7. instantiate a gridview
GridView gv = new GridView();
//8. set gridview datasource to the dataset
gv.DataSource = ds.Tables[0];
//9. bind gridview
gv.DataBind();
//10. tell the gridview to render itself into htmltextwriter
gv.RenderControl(htmlWrite);
//11. output html
response.Write(stringWrite.ToString());
//12. end response
response.End();
}
catch (Exception ex)
{
string str = ex.Message;
}
}
I've found that Response.Write doesn't work with AJAX. Are there any alternatives to this?
I am generating an Excel file upon a click of a button in an update panel. It is throwing a parsing error.
If I keep the button outside the update panel it is working fine. Why isn't it working in the update Panel?
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition",
String.Format("attachment;filename={0}", filename))
Response.Clear()
Response.BinaryWrite(WriteToStream.GetBuffer)
HttpContext.Current.ApplicationInstance.CompleteRequest()
Private Function WriteToStream() As MemoryStream
'Write the stream data of workbook to the root directory
Dim file As MemoryStream = New MemoryStream
hssfworkbook.Write(file)
Return file
End Function
Im writing a bit of code that will extract the contents of a gridview to excel. I have all the parts working except for one. In my page_load i fetch the data from the database and then have the user select some options which filters the data and then the gridview gets populated. The problem im having is that the Response call doesnt seem to get the data from the gridview if i enclose the database fetch command within if not page.ispostback in the page_load
I have some test pages where i just fetched the data from the database bound it in a gridview in the Page_load. then on a button click export the data in the gridview to excel. Works fine if i DONT enclose the fetch command within not page.ispostback but outputs an empty excel file if i do.
Now i have to use not page.ispostback in the actual webpages because the users have to make selections which will filter the original dataset. How do i get this to work?
The page is also implementing AJAX if that helps. Here's an example of what i have:
[Code]....
I want to read data from an excel sheet which is in non-standard format. I have tried many things but could not find a solution,
View 1 RepliesMy existing code converting xml in to excel and it is saving excel in .xls format.
But i need to save it in .xlsx format .
Existing code for converting xml...
[Code]....
Existing code for saving excel
var dialog = new System.Windows.Forms.SaveFileDialog();
dialog.Filter = "Excel Workbook(*.xls)|*.xls";
dialog.Title = "Save As";
I have tried by changing .xls into .xlsx but unable to open excel sheet.
There was a string (1008901023816550000000) in the GridView somehow was saved as a scientific number (1.0089E+21) in Excel when I exported the GridView to Excel.I am using .Net 4.0.Here is what I tried, but they didn't solve the problem:
Added DataFormatString="{0:g}" to the BoundColumn tag.
Set the style after the RenderControl was called.
string style = @" .text { mso-number-format:@; } ";
i have some excel file which export from sybase, but it can only export to Excel ver2.1.then I connect and import to SQL Database. The result is error and prompt cannot read the Excel format.Finally I try use Excel 2003 to open the exce v2.1 and save as 2003 version. Then import again, it can success to import to SQL database.my connection string excel properties = 8.0i tried to change 8.0 to 2.1 or 5.0, the result is still cannot import. how to import v2.1 directly. no need use Excel to open and save again.another question, if I want import more than one excel, how can I import all one time only.
View 2 RepliesIs there a simple way to use Response.Write to adjust column widths when exporting data to MS Excel?
View 3 RepliesI want to read data from excel while preserving format of the data.
If data is entered as below in the cell
"this is test sample data"
so my o/p should be
<p> this is test </p>
<p>sample data</p>
or something like that which preserves the data formatting.
I am trying to import the excel file whose format is not in row column order in asp.net 3.5
I tried to read the file using OleDBDataAdapter but it given me the error "External file is not in proper format" something like that..
I have this scenario where I extract the data from the database as excel format, instead of saving it some where in the drive, I want is to attach this file and send it via e-mail.
View 3 Replies