使用 Spire.Xls 导出 Excel
public static void ExportRetainCard(string exportData, string exportHeader, string fileName)
{
//将当前路径下的文件内容读取到workbook对象里面
string path = System.Windows.Forms.Application.StartupPath + "/TempletsPath/Retain Card.xlsx";
Workbook workBook = new Workbook();
//将当前路径下的文件内容读取到workbook对象里面
workBook.LoadFromFile(path);
//得到第一个Sheet页
Worksheet sheet = workBook.Worksheets[0];
var exportDataList = JsonConvert.DeserializeObject<List<RetainCardData>>(exportData);
CellRange styleHeader = sheet.Rows[2];
//添加 数据
int index = 3;
foreach (var item in exportDataList)
{
//复制行,包括行的样式
sheet.CopyRow(styleHeader, sheet, index, CopyRangeOptions.All);
sheet.Range[index, 1].Text = item.CustomerName;//Customer
sheet.Range[index, 2].Text = item.AtmCode;//ATM Code
sheet.Range[index, 3].Text = item.JobDate.ToString("yyyy-MM-dd");//Execution Date
sheet.Range[index, 4].Text = item.ItemType == 10 ? "Journal" : item.ItemType == 20 ? "Return Card" : ""; //Items Type
sheet.Range[index, 5].Text = item.CardNo;//Card No./Journal Pieces
sheet.Range[index, 6].Text = item.CardName;//Card Owner
sheet.Range[index, 7].Text = item.BanOwnerName;//Bank Owner
sheet.Range[index, 8].Text = item.CardTypeName;//Card Type
sheet.Range[index, 9].Text = item.CardStatus == 0 ? "Not Destroy" : "Destroy";//Card Status
sheet.Range[index, 10].Text = item.Slip == 1 ? "Yes" : item.Slip == 2 ? "No" : "";//Slip
sheet.Range[index, 11].Text = item.Remark;//Remark
sheet.Range[index, 12].Text = item.Status == 0 ? "Wait Pack" : item.Status == 1 ? "Packed" : item.Status == 2 ? "Deleted" : "";//Status
sheet.Range[index, 13].Text = item.ItemBarCode;//TE Barcode
sheet.Range[index, 13].Style.HorizontalAlignment = HorizontalAlignType.Right;//水平靠右
sheet.Range[index, 14].Text = (item.Source == 1 ? "CPC:" : "PDA:") + GetOperateor(item.Operateor);//Pack By
sheet.Range[index, 15].Text = item.OperateTime.ToString("yyyy-MM-dd HH:mm:ss");//Last Modify
sheet.Range[index, 16].Text = (item.Source == 1 ? "CPC:" : "PDA:") + GetOperateor(item.Operateor);//Register by
sheet.Range[index, 17].Text = item.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"); ;//Register time
index++;
}
//保存文件
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.DefaultExt = ".xlsx";
saveFile.Filter = "Excel文件|*.xlsx";
saveFile.FileName = fileName;
//弹出对话框
var result = saveFile.ShowDialog();
//点击确定
if (result.HasValue && result.Value && fileName != "")
{
try
{
//新建文件流,创建一个新文件
FileStream newFile = new FileStream(saveFile.FileName, FileMode.Create);
workBook.SaveToStream(newFile, FileFormat.Version2007);
//关闭文件流
newFile.Close();
MessageBox.Show("Export successfully");
}
catch (Exception)
{
MessageBox.Show("The File may be opened,please close it and try again");
}
}
}
/// <summary>
/// 处理显示的 操作人
/// </summary>
/// <returns></returns>
private static string GetOperateor(string Operateor)
{
string Val = string.Empty;
if (Operateor != null)
Val = Operateor.Replace("(", "|").Replace(")", "").Replace(" ", "");
return Val;
}