111,094
社区成员




List<string> cols = new List<string>();
OleDbConnection conn = new OleDbConnection(string.Format(ExcelConnstring, fileName, "Excel 8.0;HDR=Yes;IMEX=1"));//处理Excel数据类型和文本类型不确定时
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//取到第一个表的表名
string strTableName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter myCommand = null;
string strSelect = "select * from [" + strTableName + "]";
myCommand = new OleDbDataAdapter(strSelect, conn);
string dataTableName = Path.GetFileNameWithoutExtension(fileName);
this.DataSetImportCell.Tables.Clear();
myCommand.Fill(DataSetImportCell, dataTableName);
myCommand.Dispose();
foreach (DataColumn temp in DataSetImportCell.Tables[dataTableName].Columns)
{
cols.Add(temp.ColumnName);
}
conn.Close();
public class ExcelExport
{
public ExcelExport(string SQLCOndition, string title)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook xBk;
Microsoft.Office.Interop.Excel._Worksheet xSt;
Microsoft.Office.Interop.Excel._QueryTable xQt;
SqlConnection tmpUpdConn = new SqlConnection(@"Server=" + ConfigurationManager.AppSettings["DBAddr"].ToString() + ";Initial Catalog=" + ConfigurationManager.AppSettings["DBName"].ToString() + ";Uid=" + ConfigurationManager.AppSettings["UserName"].ToString() + ";Pwd=" + ConfigurationManager.AppSettings["UserPwd"].ToString() + ";");
tmpUpdConn.Open();
SqlCommand tmpUpdCmd = new SqlCommand(SQLCOndition, tmpUpdConn);
tmpUpdCmd.CommandTimeout = 6000;
SqlDataAdapter tmpada = new SqlDataAdapter(tmpUpdCmd);
DataSet tmpds = new DataSet();
tmpada.Fill(tmpds);
System.Data.DataTable tmpt = tmpds.Tables[0];
tmpUpdConn.Close();
if (tmpt.Rows.Count <= 0)
{
CreateExcel_51.App_Code.Global.isNull = true;
GC.Collect();
tmpds.Dispose();
tmpada.Dispose();
tmpt.Dispose();
return;
}
string Conn = "ODBC;DRIVER=SQL Server;SERVER=" + ConfigurationManager.AppSettings["DBAddr"].ToString() + ";UID=" + ConfigurationManager.AppSettings["UserName"].ToString() + ";PWD=" + ConfigurationManager.AppSettings["UserPwd"].ToString() + ";DATABASE=" + ConfigurationManager.AppSettings["DBName"].ToString() + "";
string Select = SQLCOndition;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), Select);
xQt.Name = "";
xQt.FieldNames = true;
xQt.RowNumbers = false;
xQt.FillAdjacentFormulas = false;
xQt.PreserveFormatting = false;
xQt.BackgroundQuery = false;
xQt.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells;
xQt.AdjustColumnWidth = true;
xQt.RefreshPeriod = 0;
xQt.PreserveColumnInfo = true;
xQt.Refresh(xQt.BackgroundQuery);
string Dir = "d:\\test\\" + ConfigurationManager.AppSettings["ExcelDir"].ToString() + "\\files_";
string FileName = CreateExcel_51.App_Code.Global.n + "_Profile.xls";
string ExcelFileName = Dir + FileName;
while (File.Exists(ExcelFileName))
{
FileName = CreateExcel_51.App_Code.Global.n + 1 + "_Profile.xls";
ExcelFileName = Dir + FileName;
CreateExcel_51.App_Code.Global.n += 1;
}
excel.ActiveWorkbook.SaveCopyAs(ExcelFileName);
excel.DisplayAlerts = false;
excel.Quit();
GC.Collect();
Console.WriteLine("xxxxxxxxx");
}
}
DataTableToExcel.ExcelExport ex = new DataTableToExcel.ExcelExport(sql条件,"");