当我们输出到EXCEL的值中有 空字符串的时候,由于输出时格式并不确定,因此系统会为空字符串自动输出成一个 ’(单引号),这样输出后的格式就会乱掉,采取一种简单的做法后可以解决。
输出的时候将所有空值 和空字符串 都转成 " " 空格字符串,于是格式乱的问题都解决了。
当我们输出到EXCEL的值中有 空字符串的时候,由于输出时格式并不确定,因此系统会为空字符串自动输出成一个 ’(单引号),这样输出后的格式就会乱掉,采取一种简单的做法后可以解决。
输出的时候将所有空值 和空字符串 都转成 " " 空格字符串,于是格式乱的问题都解决了。
附: E8提供BaseTools中的关于EXCEL 输出的 代码:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
ExportExcel.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
namespace Epower.DevBase.BaseTools
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
///
/// </summary>
public class IDataFieldProcess
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
///
/// </summary>
public IDataFieldProcess()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 处理字段结果
/// </summary>
/// <param name="lngID"></param>
/// <param name="lngAppID"></param>
/// <param name="lngOpID"></param>
/// <returns></returns>
public virtual string GetDataFieldProcess(string sFieldValue, string sPara)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return sFieldValue;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// ExcelProc 的摘要说明。
/// </summary>
public class ExportExcel
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private static OleDbConnection _oleConn;
private static OleDbCommand _oleCmdSelect;
public ExportExcel()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static string AddWithComma(string strSource,string strAdd)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (strSource !="") strSource = strSource += ", ";
return strSource + strAdd;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static OleDbDataAdapter SetSheetQueryAdapter(DataTable dt)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Deleting in Excel workbook is not possible
//So this command is not defined
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbDataAdapter oleda = new OleDbDataAdapter(_oleCmdSelect);
string strInsertPar="";
string strInsert="";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
for (int iCol=0;iCol<dt.Columns.Count;iCol++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strInsert= AddWithComma(strInsert,dt.Columns[iCol].ColumnName);
strInsertPar= AddWithComma(strInsertPar,"?");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
string strTable = "[Sheet1$]";
strInsert = "INSERT INTO "+ strTable + "(" + strInsert +") Values (" + strInsertPar + ")";
oleda.InsertCommand = new OleDbCommand(strInsert,_oleConn);
OleDbParameter oleParIns = null;
for (int iCol=0;iCol<dt.Columns.Count;iCol++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
oleParIns = new OleDbParameter("?",dt.Columns[iCol].DataType.ToString());
oleParIns.SourceColumn =dt.Columns[iCol].ColumnName;
oleda.InsertCommand.Parameters.Add(oleParIns);
oleParIns=null;
}
return oleda;
}
catch (Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
///
/// </summary>
/// <param name="tmpFileName">源文件</param>
/// <param name="toFileName">目标文件</param>
/// <param name="arrFields">字段数组</param>
/// <param name="dtSource">源数据</param>
public static void InsertExcel(string tmpFileName,string toFileName,string[] arrFields,System.Data.DataTable dtSource,IDataFieldProcess idfp)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string Tablename = "[Sheet1$]";
System.IO.File.Copy(tmpFileName,toFileName,true);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
System.IO.FileInfo fi=new System.IO.FileInfo(toFileName);
fi.Attributes=System.IO.FileAttributes.Normal;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
string strCon = @" Provider = Microsoft.Jet.OLEDB.4.0;Data Source= " + toFileName + ";Extended Properties=Excel 8.0";
_oleConn = new OleDbConnection ( strCon ) ;
string strCom = " SELECT * FROM "+ Tablename+" WHERE 1=2";
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_oleConn.Open () ;
//打开数据链接,得到一个数据集
_oleCmdSelect =new OleDbCommand(strCom, _oleConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , _oleConn);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
OleDbCommandBuilder cb=new OleDbCommandBuilder(myCommand);
//创建一个 DataTable对象
DataTable myDT = new DataTable (Tablename) ;
//得到自己的DataSet对象
myCommand.Fill(myDT) ;
foreach(DataRow drSource in dtSource.Rows)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataRow dr = myDT.NewRow();
for(int n=0;n<arrFields.Length;n++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string sFileName=arrFields[n].ToString();
string sValue = "";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (sFileName.IndexOf(":") > 0)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//如果字段名内有: 分开,则经过 一次处理后输出
string[] sArr = sFileName.Split(":".ToCharArray());
sValue = idfp.GetDataFieldProcess(drSource[sArr[0]].ToString(), sArr[1]);
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sValue = drSource[sFileName].ToString();
}
//防止空值打乱格式
if (sValue == "")
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sValue = " ";
}
dr[n] = (object)sValue;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
myDT.Rows.InsertAt(dr,0);
}
OleDbDataAdapter oleAdapter = SetSheetQueryAdapter(myDT);
oleAdapter.Update(myDT);
}
catch(Exception er)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception(er.Message);
}
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_oleConn.Close () ;
}
}
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ExportExcel.cs 里面 就有专门的处理这个情况。E8.Net用户可以直接参考这个代码进行修正
其中 IDataFieldProcess 主要是为了处理输出EXCEL时 一些字段需要转换的情况(如:状态名称等),如果全在SQL SERVER进行转换会加大 SQLSERVER的压力