以下代码由百度搬运拼凑，不懂得请先百度，时间太久了我也忘了具体逻辑
/// <summary>
/// 自定义方法，引出excel
/// </summary>
public void excelOut()
{
//定义数据表
DataSet ds;
ds = DBUtils.ExecuteDataSet(Context, sql);
ds.Tables[0].TableName = "表头";
DataSet ds2 = DBUtils.ExecuteDataSet(Context, sql2);//sql查询结果用dataset承接
DataTable dt = ds2.Tables[0];
dt.TableName = "明细";
DataTable dataTable = dt.Copy();
ds.Tables.Add(dataTable);
ds.Tables.Add(dataTable2);
ds.Tables.Remove(dataTable);
ds.Tables[1].TableName = "明细";
string fileName = string.Format("{0}.xls", Number);//设置导出文件名
var time = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");
string filePath0 = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, "BOMLB" + time);
ToExcelSheet(ds, fileName, filePath0)；
string filePath2 = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, "BOMZIP" + time);
if (!System.IO.Directory.Exists(filePath2))
{
System.IO.Directory.CreateDirectory(filePath2);//不存在就创建目录
}
//下方是压缩后导出，如果不用压缩直接用目录下面的excel导出
string filePath3 = "TempfilePath /BOMZIP" + time + "/BOM.zip";
ZipHelper.ZipDirectory(filePath0, filePath2, "BOM", false);//这里用了一个压缩方法，也放在下面
DynamicFormShowParameter param = new DynamicFormShowParameter();
param.FormId = "BOS_FileDownLoad";
param.OpenStyle.ShowType = ShowType.Modal;
param.CustomParams.Add("IsExportData", "true");
param.CustomParams.Add("url", filePath3);
this.View.ShowForm(param);
}
public void ToExcelSheet(DataSet ds, string fileName, string filepath)
{
Microsoft.Office.Interop.Excel.Application appExcel;
appExcel = new Microsoft.Office.Interop.Excel.Application();
if (appExcel == null)
{
throw new Exception("您的电脑未安装 OFFICE EXCEL");
//this.View.ShowMessage("您的电脑未安装 OFFICE EXCEL");
//return;
}
Microsoft.Office.Interop.Excel.Workbook workbookData;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
//Range range;
workbookData = appExcel.Workbooks.Add();
appExcel.DisplayAlerts = false;
for (int k = ds.Tables.Count - 1; k >= 0; k--)
{
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
// testnum--;
if (ds.Tables[k] != null)
{
worksheetData.Name = ds.Tables[k].TableName;
//写入数值
for (int r = 0; r < ds.Tables[k].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[r + 1, i + 1] = ds.Tables[k].Rows[r][i];
Range myrange1 = worksheetData.Range[worksheetData.Cells[1, i + 1], worksheetData.Cells[1, i + 1]];
Range myrange = worksheetData.Range[worksheetData.Cells[r + 2, i + 1], worksheetData.Cells[r + 3, i + 2]];
myrange1.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(50, 205, 50));
myrange.NumberFormatLocal = "@";//文本格式
//myrange.EntireColumn.AutoFit();//自动调整列宽
myrange.WrapText = true; //文本自动换行
myrange.ColumnWidth = 100;
myrange.RowHeight = 30;
//myrange1.RowHeight = 30;
}
// rowRead++;
}
}
worksheetData.Columns.EntireColumn.AutoFit();
workbookData.Saved = true;
}
workbookData.SaveAs(fileName);
// workbookData.Close();
// appExcel.Quit();
//if (true)
//{
// ApplicationClass excelApp = new ApplicationClass();
// Workbook workbook2 = excelApp.Workbooks.Open(fileName);
// excelApp.Visible = true;
//}
List<string> li = new List<string>();
li.Add(filepath + "\\" + fileName);
WriteLog(li);
appExcel.ActiveWorkbook.SaveAs(filepath + "\\" + fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
appExcel.Quit();
appExcel = null;
GC.Collect();
}
/// <summary>
/// 将List转换为DataTable
/// </summary>
/// <param name="list">请求数据</param>
/// <returns></returns>
public static DataTable ListToDataTable<T>(List<BOMEntity> list)
{
//创建一个名为"tableName"的空表
DataTable dt = new DataTable("tableName");
//创建传入对象名称的列
foreach (var item in list.FirstOrDefault().GetType().GetProperties())
{
dt.Columns.Add(item.Name);
}
//循环存储
foreach (var item in list)
{
//新加行
DataRow value = dt.NewRow();
//根据DataTable中的值，进行对应的赋值
foreach (DataColumn dtColumn in dt.Columns)
{
int i = dt.Columns.IndexOf(dtColumn);
//基元元素，直接复制，对象类型等，进行序列化
if (value.GetType().IsPrimitive)
{
value[i] = item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item, null);
}
else
{
string a = JsonConvert.SerializeObject(item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item, null)).ToString().Replace("\\\"", "").Trim('"').Replace("\\r\\n", "\r\n");
value[i] = a;
}
}
dt.Rows.Add(value);
}
return dt;
}
推荐阅读