Export from dataset to Excel c# .net

  • Thread starter Thread starter shan_chennai
  • Start date Start date
S

shan_chennai

Hi,

I export data from a datset to excel by the code shown below. The
problem iam getting is that the data with empty strings in datset is
shown as 17 white spaces in the exported excel file.
ie: '' becomes ' ' ??

Please Help ASAP


XmlDataDocument _xmlDoc = new XmlDataDocument(_oLocalDS);
XslTransform _xslTran = new XslTransform();

_xslTran.Load(_stXSLTemplatePath);
stNetusePath =
oIOop.CheckServerinArray(_stExportedXmlFileName,true);
_oXMLTextWriter = new XmlTextWriter(_stExportedXmlFileName,
System.Text.Encoding.UTF8);
_oXMLTextWriter.Formatting = Formatting.Indented;
_oXMLTextWriter.Indentation = 3;
_oXMLTextWriter.WriteStartDocument();
_xslTran.Transform(_xmlDoc, null, _oXMLTextWriter);
_oXMLTextWriter.Close();
_oXMLTextWriter = null;
_xslTran = null;
if (stNetusePath != "")
oIOop.CloseConnection(stNetusePath);
//Convert Excel in XML format to xls format
ConvertXmltoXls(_stExportedXmlFileName ,_stExportedFileName);
 
Shan,

You will have to post the code for the ConvertXmltoXls routine, as that
is where the problem is most likely. The code attached doesn't show the
interaction with Excel at all.
 
The code is as follows...

//convert the xml format file to excel format file
private void ConvertXmltoXls(string _stExportedXmlFileName,string
_stExportedFileName)
{
try
{
Excel.Application _oExcelApp = new Excel.ApplicationClass();
Excel.Workbook _oWorkBook;

object missing = System.Reflection.Missing.Value;
object _oFileName =null;
string stNetUsepath1="";
string stNetUsepath2="";
IOOperation oIOop= new IOOperation();

try
{

_oFileName = _stExportedFileName;
object oFileFormat = Excel.XlFileFormat.xlWorkbookNormal;

//if the file to be created exists already, in order to overwrite
it, delete the file and again save the given xml file in the same name.
if (File.Exists(_stExportedFileName))
File.Delete(_stExportedFileName);
stNetUsepath1 =
oIOop.CheckServerinArray(_stExportedXmlFileName,true);
stNetUsepath2 =
oIOop.CheckServerinArray(_oFileName.ToString(),true);
//open the file
_oWorkBook =
_oExcelApp.Workbooks.Open(_stExportedXmlFileName,missing,missing,5,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);

//save the file in excel format.
_oWorkBook.SaveAs(_oFileName,oFileFormat,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlNoChange
,missing,missing,missing,missing,missing);
//close the xml file.
_oWorkBook.Close(missing,missing,missing);
NAR(_oWorkBook);
/* if(_stProcessCode == "EPO")
{

_oWorkBook =
_oExcelApp.Workbooks.Open(_oFileName.ToString(),missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
//Excel.Worksheets oSheets = _oWorkBook.Sheets;

((Excel.Worksheet)_oWorkBook.Sheets[1]).Protect(Pswd,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, true,
Type.Missing, Type.Missing);
_oWorkBook.Protect(Pswd,Type.Missing,Type.Missing);
_oWorkBook.Save();
_oWorkBook.Close(SaveChanges ,_oFileName,missing);
}*/

}
catch(Exception oEx)
{
throw oEx;
}
finally
{

_oWorkBook = null;
_oExcelApp.Quit();
NAR(_oExcelApp);
_oExcelApp = null;
if (stNetUsepath1.Trim() !="")
{
oIOop.CloseConnection(stNetUsepath2);
oIOop.CloseConnection(Path.GetPathRoot(_stExportedFileName));
}
if (stNetUsepath2.Trim() !="" && _oFileName !=null)
{
oIOop.CloseConnection(stNetUsepath2);
oIOop.CloseConnection(Path.GetPathRoot(_oFileName.ToString()));
}

}
}
catch(Exception oEx1)
{
throw oEx1;
}
}
 
Back
Top