"NPOI.dll"을 이용한 엑셀 다운로드 방법

"NPOI.dll"을 이용한 엑셀 다운로드 방법.

public ActionResult DownloadExcel(string serviceNationCode, int qprimeTypeId, string originNationCode, string destinationNationCode)

        {

            int qprimeContractCompanyCode = GetQprimeContractCompanyCode(serviceNationCode, qprimeTypeId, originNationCode, destinationNationCode);

            RateTable[] rateTableInfo = GetRateTable("DE", qprimeContractCompanyCode, "", "", "").ToArray();

            

            var workbook = new HSSFWorkbook();

            IFont boldFont = workbook.CreateFont();

            boldFont.IsBold = true;

            ICellStyle styleMiddle = workbook.CreateCellStyle();

            styleMiddle.Alignment = HorizontalAlignment.Center;

            styleMiddle.VerticalAlignment = VerticalAlignment.Center;

            styleMiddle.WrapText = true;

            styleMiddle.SetFont(boldFont);

            styleMiddle.BorderBottom = BorderStyle.Thick;

            var sheet = workbook.CreateSheet("Qprime Rate Table");            

            sheet.SetColumnWidth(0, 3500);

            sheet.SetColumnWidth(1, 3500);

            sheet.SetColumnWidth(2, 4000);

            sheet.SetColumnWidth(3, 4000);

            sheet.SetColumnWidth(4, 4000);

            sheet.SetColumnWidth(5, 4000);

            sheet.SetColumnWidth(6, 4000);

            sheet.SetColumnWidth(7, 4000);

            sheet.SetColumnWidth(8, 4000);

            sheet.SetColumnWidth(9, 4000);

            var headers = new[] { "Weight From (kg)", "Weight To (kg)", "Shipping Rate (SGD)", "Shipping Rate (KRW)", "Shipping Rate (JPY)", "Shipping Rate (MYR)", "Shipping Rate (IDR)", "Shipping Rate (USD)", "Shipping Rate (CNY)", "Shipping Rate (HKD)" };

            var columns = new[] { "Weight From (kg)", "Weight To (kg)", "Shipping Rate (SGD)", "Shipping Rate (KRW)", "Shipping Rate (JPY)", "Shipping Rate (MYR)", "Shipping Rate (IDR)", "Shipping Rate (USD)", "Shipping Rate (CNY)", "Shipping Rate (HKD)" };

            IRow headerRow = sheet.CreateRow(0);

            headerRow.Height = 700;

            ICell hearderCell = null;

            for (int i = 0; i < columns.Length; i++)

            {

                hearderCell = headerRow.CreateCell(i);

                hearderCell.CellStyle = styleMiddle;               

                hearderCell.SetCellValue(new HSSFRichTextString(headers[i]));                 

            }

            int rowIndex = 0;

            IRow columnRow = null;

            for (int i = 0; i < rateTableInfo.Length; i++)

            {

                rowIndex += 1;

                columnRow = sheet.CreateRow(rowIndex);

                columnRow.Height = 350;

                columnRow.CreateCell(0).SetCellValue(CheckDecimal(rateTableInfo[i].WeightFrom).ToString());

                columnRow.CreateCell(1).SetCellValue(CheckDecimal(rateTableInfo[i].WeightTo).ToString());

                columnRow.CreateCell(2).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateSG).ToString());

                columnRow.CreateCell(3).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateKR).ToString());

                columnRow.CreateCell(4).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateJP).ToString());

                columnRow.CreateCell(5).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateMY).ToString());

                columnRow.CreateCell(6).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateID).ToString());

                columnRow.CreateCell(7).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateUS).ToString());

                columnRow.CreateCell(8).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateCN).ToString());

                columnRow.CreateCell(9).SetCellValue(CheckDecimal(rateTableInfo[i].ShippingRateHK).ToString());

            }

            using (var memoryStream = new MemoryStream())

            {

                workbook.Write(memoryStream);

                byte[] bytes = memoryStream.ToArray();

                if (bytes.Length > 0)

                {

                    return File(bytes, "application/vnd.ms-excel", "Qprime Reate Table.xls");

                }

            }

            return View();

        }

        public ActionResult About()

        {

            return View();

        }

        public ActionResult RateTable()

        {

            return View();

        }

    }