导出方法:

C# 导出excel的方法_json


代码:

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

{ }

}