public static bool ExportToExcel_dataSet(string queryNo, string conditions)
{
bool _bl = false;
try
{
Docmd.cnnOpen();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "dbo.sx_pro_QueryTest";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Docmd.cnn;
cmd.CommandTimeout = 180;
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.Parameters.AddWithValue("@QueryNo",queryNo);
cmd.Parameters.AddWithValue("@Whr", conditions);
DataSet ds = new DataSet();
da.Fill(ds,"t1");
DataTable dt = ds.Tables["t1"];
int qtyRows = dt.Rows.Count;
if(qtyRows==0)
{
Docmd.msgInfo("no records found. \r\n 没有找到记录");
goto line1;
}
int qtyCols = dt.Columns.Count;
int rn = 0;
int cn = 0;
object[,] objTitle = new object[1, qtyCols];
foreach (DataColumn dc in dt.Columns)
{
objTitle[0, cn] = dc.ColumnName;
cn++;
}
object[,] objData = new object[qtyRows, qtyCols];
foreach (DataRow dr in dt.Rows)
{
cn = 0;
foreach (DataColumn dc in dt.Columns)
{
objData[rn, cn] = dr[dc].ToString();
cn++;
}
rn++;
}
excel.Application xlApp = new excel.Application();
excel.Workbook wk = xlApp.Workbooks.Add();
excel.Worksheet sht = wk.Worksheets[1];
xlApp.Visible = true;
excel.Range myRange_Title = sht.Range[sht.Cells[1, 1], sht.Cells[1, qtyCols]];
myRange_Title.Value2 = objTitle;
excel.Range myRange = sht.Range[sht.Cells[2, 1], sht.Cells[qtyRows + 1, qtyCols]];
myRange.Value2 = objData;
sht.UsedRange.WrapText = false;
line1:
ds.Dispose();
cmd.Dispose();
}
catch(Exception ex)
{
Docmd.msgInfo(ex.Message);
}
finally
{
Docmd.cnnClose();
}
return _bl;
}