public bool ToExcel(string tableName, string xLSFileName, string Title, string Filter, string Sort, List<string> IgnoreColumns)
{
bool rtn = false;
DataSetToExcel dte = new DataSetToExcel();
dte.DataSet = this;
dte.Excelpath = xLSFileName;
dte.Title = Title;
dte.Filter = Filter;
dte.Sort = Sort;
dte.IgnoreColumns = IgnoreColumns;
if (this.RealDataSet.Tables.Contains(tableName))
{
rtn = dte.Export(tableName);
}
else
{
rtn = false;
}
return rtn;
}
public void ToExcel()
{
if (!string.IsNullOrEmpty(this.DataSourceID))
{
object obj = this.GetObjByID(this.DataSourceID);
if (obj != null && obj is WebDataSource)
{
//取出所有资料
WebDataSource wds = obj as WebDataSource;
DataView view = wds.View;
List<string> columns = new List<string>();
Hashtable controlTable = new Hashtable();//存放WebRefVal
InfoDataSet ids = new InfoDataSet();
ids.PacketRecords = -1;
ids.RemoteName = wds.RemoteName;
DataSet ds = ids.RealDataSet;
ds.Tables.Add(wds.View.Table.TableName);
for (int i = 0; i < this.Columns.Count; i++)
{
if (this.Columns[i].Visible)
{
if (this.Columns[i] is BoundField)
{
columns.Add((this.Columns[i] as BoundField).DataField);
ds.Tables[0].Columns.Add((this.Columns[i] as BoundField).DataField);
}
else if (this.Columns[i] is TemplateField && !string.IsNullOrEmpty(this.Columns[i].SortExpression))
{
columns.Add(this.Columns[i].SortExpression);
ds.Tables[0].Columns.Add(this.Columns[i].SortExpression);
if (this.Rows.Count > 0)
{
foreach (Control ctrl in this.Rows[0].Cells[i].Controls)
{
if (ctrl is WebRefVal)
{
controlTable.Add(this.Columns[i].SortExpression, ctrl);
break;
}
}
}
}
}
}
for (int i = 0; i < view.Count; i++)
{
object[] values = new object[columns.Count];
for (int j = 0; j < columns.Count; j++)
{
object value = view[i][columns[j]];
if (controlTable.Contains(columns[j]))
{
if (value != null)
{
WebRefVal webRefVal = controlTable[columns[j]] as WebRefVal;
values[j] = webRefVal.GetDisplayByValue(value.ToString());
}
}
else
{
values[j] = value;
}
}
ds.Tables[0].Rows.Add(values);
}
ds.Tables[0].AcceptChanges();
string path = this.Page.MapPath(this.Page.AppRelativeVirtualPath);
string directory = Path.GetDirectoryName(path);
string filename = Path.GetFileNameWithoutExtension(path);
path = string.Format("{0}\\ExcelDoc\\{1}", directory, string.Format("{0}-{1:yyMMddHHmmss}", filename, DateTime.Now));
path = Path.ChangeExtension(path, "xls");
DataSetToExcel dste = new DataSetToExcel();
dste.Excelpath = path;
dste.DataSet = ids;
dste.Export();
FileInfo file = new FileInfo(path);
System.Web.HttpResponse Response = this.Page.Response;
Response.Clear();
Response.Buffer = false;
Response.ContentType = "application/x-msdownload";
Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
Response.AddHeader("Content-Length", file.Length.ToString());
Response.Filter.Close();
Response.WriteFile(file.FullName);
Response.End();
}
}
}
public string ToExcel(int TableIndex, string Filter, string Sort, bool Open, string Title, List<string> IgnoreColumns)
{
string path = this.Page.MapPath(this.Page.AppRelativeVirtualPath);
string directory = Path.GetDirectoryName(path);
string filename = Path.GetFileNameWithoutExtension(path);
path = string.Format("{0}\\ExcelDoc\\{1}", directory, string.Format("{0}-{1:yyMMddHHmmss}", filename, DateTime.Now));
path = Path.ChangeExtension(path, "xls");
string newfilename = Path.GetFileName(path);
InfoDataSet ids = new InfoDataSet();
if (this.InnerDataSet != null)
{
ids.RealDataSet = this.InnerDataSet;
ids.RemoteName = this.RemoteName;//DD Use
ids.PacketRecords = this.PacketRecords;
ids.LastIndex = this.LastIndex;
}
else
{
DataTable tb = this.CommandTable.Clone();
tb.TableName = "CommandTable";
tb.Merge(this.CommandTable);
ids.RealDataSet.Tables.Add(tb);
}
ids.WhereStr = this.WhereStr;
ids.WhereParam = this.WhereParam;
DataSetToExcel dste = new DataSetToExcel();
dste.Excelpath = path;
dste.Filter = Filter;
dste.Sort = Sort;
dste.DataSet = ids;
dste.Title = Title;
dste.IgnoreColumns = IgnoreColumns;
try
{
dste.Export(TableIndex);
}
finally
{
this.LastIndex = ids.LastIndex;
}
if (Open)
{
this.Export(CliUtils.ReplaceFileName(this.Page.AppRelativeVirtualPath, filename + ".aspx", string.Format("ExcelDoc/{0}", newfilename)));
}
return path;
}
private void DoExport()
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel file(*.xls)|*.xls";
sfd.Title = "Export to File";
sfd.AddExtension = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
InfoDataSet ids = new InfoDataSet();
if (this.ViewBindingSource != null)
{
ids = this.ViewBindingSource.GetDataSource() as InfoDataSet;
}
else
{
ids = this.BindingSource.GetDataSource() as InfoDataSet;
}
DataSetToExcel dste = new DataSetToExcel();
dste.Excelpath = sfd.FileName;
dste.DataSet = ids;
dste.BeforeExport = new BeforeExportEventHandler(BeforeExportXLS);
dste.Export();
// toexcel;
}
}