Y.J. Work

本來Excel在寫入的時候大部分都是習慣一格一格寫入

但是久了就覺得重複程式打起來有點煩人

於是就稍微簡化了一下方式

首先先建一個Class

public class ExcelData
{
    public int page { get; set; } //頁數
    public string position { get; set; } //欄位
    public string value { get; set; } //數值
    public string type { get; set; }  // 類別（文字或是圖片）
}

然後就可以在需要寫入Excel的地方新建一個List<ExcelData>

將要寫入的資料放入List中

大概是像下面這樣

lstExcel.Add(new ExcelData
{
    page = pagenum,
    position = "A6",
    value = data.code,
    type = "text"
});
lstExcel.Add(new ExcelData
{
    page = pagenum,
    position = "B6" ,
    value = data.name,
    type = "text"
});
lstExcel.Add(new ExcelData
{
    page = pagenum,
    position = "C6",
    value = data.picture,
    type = "img"
});

接下來再新建一個 workbook將List資料寫入

using (XLWorkbook workBook = XLWorkbook.OpenFromTemplate(_hostingEnvironment.WebRootPath+"/template/sample.xlsx"))
{
    //建立所需頁數
    for (var i = 1; i < pagenum + 1; i++)
    {
        workBook.Worksheet(1).CopyTo("Sheet" + (i + 1));
    }

    for (var i = 0; i < pagenum + 1; i++)
    {
        var workSheet = workBook.Worksheet(i + 1);
        #region 文字

        var textdata = lstExcel.Where(x => x.page == i && x.type == "text").ToList();
        foreach (var data in textdata)
        {
            workSheet.Cell(data.position).Value = data.value;
        }

        #endregion

        #region 圖片

        var imgdata = lstExcel.Where(x => x.page == i && x.type == "img").ToList();
        foreach (var data in imgdata)
        {
            var path = _hostingEnvironment.WebRootPath + data.value;
            if (File.Exists(path))
            {
                var image = workSheet.AddPicture(path);
                var size = 280;//圖片大小
                image.MoveTo(workSheet.Cell(data.position));
                image.Height = size;
                image.Width = Convert.ToInt32(image.Height / Convert.ToDouble(image.OriginalHeight) *
                                              Convert.ToDouble(image.OriginalWidth)); //維持長寬比
                if (image.Width > size) //避免橫向超過預設大小
                {
                    image.Width = size;
                    image.Height = Convert.ToInt32(image.Width / Convert.ToDouble(image.OriginalWidth) *
                                                   Convert.ToDouble(image.OriginalHeight));
                }

                image.Scale(0.3);
            }
        }
        
        #endregion
    }
}

如此就可以不用一直打workSheet.Cell("XX").Value = value; 了