C# 导出excel的方法

参考文档：https://blog.youkuaiyun.com/xinjitmzy/article/details/100516336

导出方法：

 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

代码：

private void skinButton2_Click(object sender, EventArgs e)
{
try
{
sc.isAll = true;
url = StatisticHttpUrl + "GetBillCodeUpDownInfo/";
string result = HttpCommon.GetInstance.HttpPostJson(url, JsonConvert.SerializeObject(sc));
searchResult = JsonConvert.DeserializeObject<SearchResult>(result);
string fileName = appSetting._LanguagePackage["file"] + DateTime.Now.ToString("yyyyMMdd");
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show(appSetting._LanguagePackage["noExcelMsg"]);
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Name = "XCUpDownBillCodeInfo";//改sheet1名为【XCUpDownBillCodeInfo】

try
{
//写入标题
worksheet.Cells[1, 1] = appSetting._LanguagePackage["billCode"];
worksheet.Cells[1, 2] = appSetting._LanguagePackage["clientId"];
worksheet.Cells[1, 3] = appSetting._LanguagePackage["createTime"];
worksheet.Cells[1, 4] = appSetting._LanguagePackage["downTime"];
worksheet.Cells[1, 5] = appSetting._LanguagePackage["carNo"];
worksheet.Cells[1, 6] = appSetting._LanguagePackage["weight"];
worksheet.Cells[1, 7] = appSetting._LanguagePackage["distributeLatticeNo"];
worksheet.Cells[1, 8] = appSetting._LanguagePackage["RealLatticeNo"];
worksheet.Cells[1, 9] = appSetting._LanguagePackage["packageNo"];
worksheet.Cells[1, 10] = appSetting._LanguagePackage["isuploadSto"];
worksheet.Cells[1, 11] = appSetting._LanguagePackage["packageType"];
worksheet.Cells[1, 12] = appSetting._LanguagePackage["code1"];
worksheet.Cells[1, 13] = appSetting._LanguagePackage["code2"];
worksheet.Cells[1, 14] = appSetting._LanguagePackage["code3"];
worksheet.Cells[1, 15] = appSetting._LanguagePackage["code4"];
worksheet.Cells[1, 16] = appSetting._LanguagePackage["endCode"];
List<ExpressInfoItem> expressInfoItems = searchResult.expressInfoItemList;
LogHelper<BillCodeInfo>.Debug("Count:"+ expressInfoItems.Count);

string[,] write_data = new string[expressInfoItems.Count+2, 17];

for (int i = 0; i < expressInfoItems.Count; i++)
{
write_data[i, 0] = expressInfoItems[i].BillCode; //单号
write_data[i, 1] = expressInfoItems[i].ClientId; //机器号
write_data[i, 2] = expressInfoItems[i].CreateTime; //创建时间
write_data[i, 3] = expressInfoItems[i].DownTime; //落格时间
write_data[i, 4] = expressInfoItems[i].CarNo; //小车号
write_data[i, 5] = expressInfoItems[i].Weight; //重量
write_data[i, 6] = expressInfoItems[i].LatticeNo; //分配的格口号
write_data[i, 7] = expressInfoItems[i].RealLatticeNo; //真实的格口号
write_data[i, 8] = expressInfoItems[i].PackgeNo; //大包号
write_data[i, 9] = GetIsUploadStr(expressInfoItems[i].IsUploadSto); //是否上传
write_data[i, 10] = ExceptionType.GetExceptionTypeStr(expressInfoItems[i].ExceptionBillType); //包裹类型
write_data[i, 11] = expressInfoItems[i].code1; //一段码
write_data[i, 12] = expressInfoItems[i].code2; //二段码
write_data[i, 13] = expressInfoItems[i].code3; //三段码
write_data[i, 14] = expressInfoItems[i].code4; //四段码
write_data[i, 15] = expressInfoItems[i].endCode; //末端码
}

Microsoft.Office.Interop.Excel.Range range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[expressInfoItems.Count+1, 16]];
range.Value = write_data;
}
catch (Exception ex)
{
LogHelper<BillCodeInfo>.Error($"用户{Program.UserInfo.userName}导出格口信息失败：", ex);
}
System.Windows.Forms.Application.DoEvents();

//影响速度
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
LogHelper<BillCodeInfo>.Debug("SaveCopyAs begin");
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
LogHelper<BillCodeInfo>.Debug("SaveCopyAs over");

MessageBox.Show(saveFileName + appSetting._LanguagePackage["dataSaved"], appSetting._LanguagePackage["tips"], MessageBoxButtons.OK);
LogHelper<BillCodeInfo>.Debug($"用户{Program.UserInfo.userName}导出格口信息成功");
}
catch (Exception ex)
{
MessageBox.Show(appSetting._LanguagePackage["exportFailMsg"] + "\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁

}
catch
{ }
}