使用 Spire.Xls 导出 Excel

使用 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;
    }