/*使用示例：
*
* DataSet ds=(DataSet)Session["AdBrowseHitDayList"];
* string ExcelFolder=Assistant.GetConfigString("ExcelFolder");
* string FilePath=Server.MapPath(".")+"\\"+ExcelFolder+"\\";
*
* //生成列的中文对应表
* Hashtable nameList = new Hashtable();
* nameList.Add("ADID", "广告编码");
* nameList.Add("ADName", "广告名称");
* nameList.Add("year", "年");
* nameList.Add("month", "月");
* nameList.Add("browsum", "显示数");
* nameList.Add("hitsum", "点击数");
* nameList.Add("BrowsinglIP", "独立IP显示");
* nameList.Add("HitsinglIP", "独立IP点击");
* //利用excel对象
* DataToExcel dte=new DataToExcel();
* string filename="";
* try
* {
* if(ds.Tables[0].Rows.Count>0)
* {
* filename=dte.DataExcel(ds.Tables[0],"标题",FilePath,nameList);
* }
* }
* catch
* {
* //dte.KillExcelProcess();
* }
*
* if(filename!="")
* {
* Response.Redirect(ExcelFolder+"\\"+filename,true);
* }
*
* */
#endregion
/// <summary>
/// 将DataTable的数据导出显示为报表(不使用Excel对象)
/// </summary>
/// <param name="dt">数据DataTable</param>
/// <param name="strTitle">标题,默认为标题</param>
/// <param name="FilePath">生成文件的路径,不包含文件名</param>
/// <param name="nameList">生成列的中文对应表，如：Hashtable nameList = new Hashtable();nameList.Add("ADID", "广告编码");</param>
/// <returns>返回生成成功后的文件名</returns>
public string DataExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList)
{
COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
get_czsj();
//ClearFile(FilePath);
string filename = DateTime.Now.ToLongDateString().ToString() + ".xls";
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel files(*.xls)|*.xls";
if (strTitle.Trim() != "" && !strTitle.Equals("标题"))
{
sfd.FileName = DateTime.Now.ToLongDateString().ToString() + "(" + strTitle + ")" + ".xls";
}
else
{
sfd.FileName = DateTime.Now.ToLongDateString().ToString() + ".xls";
}
if (sfd.ShowDialog() == DialogResult.OK)
{
get_czsj();
string localFilePath = sfd.FileName;
filename = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
excel.CreateFile(FilePath + filename);
excel.PrintGridLines = false;
COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
double height = 1.5;
excel.SetMargin(ref mt1, ref height);
excel.SetMargin(ref mt2, ref height);
excel.SetMargin(ref mt3, ref height);
excel.SetMargin(ref mt4, ref height);
COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font = "宋体";
short fontsize = 9;
excel.SetFont(ref font, ref fontsize, ref ff);
byte b1 = 1,
b2 = 12;
short s3 = 12;
excel.SetColumnWidth(ref b1, ref b2, ref s3);
string header = "页眉";
string footer = "页脚";
excel.SetHeader(ref header);
excel.SetFooter(ref footer);
COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
// 报表标题
int cellformat = 1;
int rowindex = 1, colindex = 3;
object title = (object)strTitle;
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowindex, ref colindex, ref title, ref cellformat);
int rowIndex = 1;//起始行
int colIndex = 0;
////取得列标题
//foreach (DataColumn colhead in dt.Columns)
//{
// colIndex++;
// string name = colhead.ColumnName.Trim();
// foreach (DictionaryEntry obj in nameList)
// {
// //if (obj.key.ToString() == name)
// if(obj.Key.ToString()==name)
// {
// object namestr = (object)name;
// IDictionaryEnumerator Enum = nameList.GetEnumerator();
// while (Enum.MoveNext())
// {
// if (Enum.Key.ToString().Trim() == name)
// {
// namestr = Enum.Value;
// }
// }
// excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
// }
// }
//}
foreach (DataColumn colhead in dt.Columns)
{
foreach (DictionaryEntry de in nameList)
{
if (de.Key.ToString() == colhead.ColumnName)
{
colIndex++;
string name = colhead.ColumnName.Trim();
object namestr = (object)name;
IDictionaryEnumerator Enum = nameList.GetEnumerator();
while (Enum.MoveNext())
{
if (Enum.Key.ToString().Trim() == name)
{
namestr = Enum.Value;
}
}
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
}
}
//if(colhead.ColumnName
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
foreach (DictionaryEntry obj in nameList)
{
if (obj.Key.ToString() == col.ColumnName)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
object str = (object)(string.Format("{0:yyyy-MM-dd HH:mm:ss}", Convert.ToDateTime(row[col.ColumnName])));
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
else
{
object str = (object)row[col.ColumnName].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
}
}
}
}
}
else
{
filename = "";
}
int ret = excel.CloseFile();
// if(ret!=0)
// {
// //MessageBox.Show(this,"Error!");
// }
// else
// {
// //MessageBox.Show(this,"请打开文件c:\\test.xls!");
// }
Cursor.Current = Cursors.Default;
return(filename);
}
/*使用示例：
* DataSet ds=(DataSet)Session["AdBrowseHitDayList"];
* string ExcelFolder=Assistant.GetConfigString("ExcelFolder");
* string FilePath=Server.MapPath(".")+"\\"+ExcelFolder+"\\";
*
* //生成列的中文对应表
* Hashtable nameList = new Hashtable();
* nameList.Add("ADID", "广告编码");
* nameList.Add("ADName", "广告名称");
* nameList.Add("year", "年");
* nameList.Add("month", "月");
* nameList.Add("browsum", "显示数");
* nameList.Add("hitsum", "点击数");
* nameList.Add("BrowsinglIP", "独立IP显示");
* nameList.Add("HitsinglIP", "独立IP点击");
* //利用excel对象
* DataToExcel dte=new DataToExcel();
* string filename="";
* try
* {
* if(ds.Tables[0].Rows.Count>0)
* {
* filename=dte.DataExcel(ds.Tables[0],"标题",FilePath,nameList);
* }
* }
* catch
* {
* //dte.KillExcelProcess();
* }
*
* if(filename!="")
* {
* Response.Redirect(ExcelFolder+"\\"+filename,true);
* }
*
* */
#endregion
/// <summary>
/// 将DataTable的数据导出显示为报表(不使用Excel对象)
/// </summary>
/// <param name="dt">数据DataTable</param>
/// <param name="strTitle">标题</param>
/// <param name="FilePath">生成文件的路径</param>
/// <param name="nameList"></param>
/// <returns></returns>
public string DataExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList)
{
COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.CreateFile(FilePath + filename);
excel.PrintGridLines = false;
COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
double height = 1.5;
excel.SetMargin(ref mt1, ref height);
excel.SetMargin(ref mt2, ref height);
excel.SetMargin(ref mt3, ref height);
excel.SetMargin(ref mt4, ref height);
COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font = "宋体";
short fontsize = 9;
excel.SetFont(ref font, ref fontsize, ref ff);
byte b1 = 1,
b2 = 12;
short s3 = 12;
excel.SetColumnWidth(ref b1, ref b2, ref s3);
string header = "页眉";
string footer = "页脚";
excel.SetHeader(ref header);
excel.SetFooter(ref footer);
COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
// 报表标题
int cellformat = 1;
// int rowindex = 1,colindex = 3;
// object title = (object)strTitle;
// excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);
int rowIndex = 1;//起始行
int colIndex = 0;
//取得列标题
foreach (DataColumn colhead in dt.Columns)
{
colIndex++;
string name = colhead.ColumnName.Trim();
object namestr = (object)name;
IDictionaryEnumerator Enum = nameList.GetEnumerator();
while (Enum.MoveNext())
{
if (Enum.Key.ToString().Trim() == name)
{
namestr = Enum.Value;
}
}
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");;
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
else
{
object str = (object)row[col.ColumnName].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
}
}
int ret = excel.CloseFile();
// if(ret!=0)
// {
// //MessageBox.Show(this,"Error!");
// }
// else
// {
// //MessageBox.Show(this,"请打开文件c:\\test.xls!");
// }
return(filename);
}
/*使用示例：
* DataSet ds=(DataSet)Session["AdBrowseHitDayList"];
* string ExcelFolder=Assistant.GetConfigString("ExcelFolder");
* string FilePath=Server.MapPath(".")+"\\"+ExcelFolder+"\\";
*
* //生成列的中文对应表
* Hashtable nameList = new Hashtable();
* nameList.Add("ADID", "广告编码");
* nameList.Add("ADName", "广告名称");
* nameList.Add("year", "年");
* nameList.Add("month", "月");
* nameList.Add("browsum", "显示数");
* nameList.Add("hitsum", "点击数");
* nameList.Add("BrowsinglIP", "独立IP显示");
* nameList.Add("HitsinglIP", "独立IP点击");
* //利用excel对象
* DataToExcel dte=new DataToExcel();
* string filename="";
* try
* {
* if(ds.Tables[0].Rows.Count>0)
* {
* filename=dte.DataExcel(ds.Tables[0],"标题",FilePath,nameList);
* }
* }
* catch
* {
* //dte.KillExcelProcess();
* }
*
* if(filename!="")
* {
* Response.Redirect(ExcelFolder+"\\"+filename,true);
* }
*
* */
#endregion
/// <summary>
/// 将DataTable的数据导出显示为报表(不使用Excel对象)
/// </summary>
/// <param name="dt">数据DataTable</param>
/// <param name="strTitle">标题</param>
/// <param name="FilePath">生成文件的路径</param>
/// <param name="nameList"></param>
/// <returns></returns>
public void DataExcel(System.Data.DataTable dt, string strTitle, string fileName, Hashtable nameList)
{
try
{
COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = false;
saveFileDialog.Title = "导出Excel文件到";
DateTime now = DateTime.Now;
saveFileDialog.FileName = fileName + "(" + DateTime.Now.ToFileTime() + ")";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
excel.CreateFile(saveFileDialog.FileName);
excel.PrintGridLines = false;
COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
double height = 1.5;
excel.SetMargin(ref mt1, ref height);
excel.SetMargin(ref mt2, ref height);
excel.SetMargin(ref mt3, ref height);
excel.SetMargin(ref mt4, ref height);
COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font = "宋体";
short fontsize = 9;
excel.SetFont(ref font, ref fontsize, ref ff);
byte b1 = 1,
b2 = 12;
short s3 = 12;
excel.SetColumnWidth(ref b1, ref b2, ref s3);
string header = "页眉";
string footer = "页脚";
excel.SetHeader(ref header);
excel.SetFooter(ref footer);
COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
// 报表标题
int cellformat = 1;
// int rowindex = 1,colindex = 3;
// object title = (object)strTitle;
// excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);
int rowIndex = 1;//起始行
int colIndex = 0;
//取得列标题
foreach (DataColumn colhead in dt.Columns)
{
colIndex++;
string name = colhead.ColumnName.Trim();
object namestr = (object)name;
IDictionaryEnumerator Enum = nameList.GetEnumerator();
while (Enum.MoveNext())
{
if (Enum.Key.ToString().Trim() == name)
{
namestr = Enum.Value;
}
}
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");;
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
else
{
object str = (object)row[col.ColumnName].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
}
}
int ret = excel.CloseFile();
if (ret != 0)
{
MessageBox.Show("导出文档失败，请稍后再试! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("导出数据成功，请查看相关的报表! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show("导出文档失败，错误: " + ex.Message, "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
