Beispiel #1
0
 private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter)
 {
     foreach (IRow r in sh)
     {
         XSSFRow row = (XSSFRow)r;
         updateRowFormulas(row, Shifter);
     }
 }
Beispiel #2
0
 public XSSFRowShifter(XSSFSheet sh)
 {
     sheet = sh;
 }
Beispiel #3
0
        //If this code does not exist in the file, add the code into the file after the following line:

        protected void CheckBT_Click(object sender, EventArgs e)
        {
            ////引用//ReferenceCode/ExcelColumn.cs的類別
            //ExcelImportTemplate GetExcelDefine = new ExcelImportTemplate();
            ////建立匯入table
            //GetExcelDefine.F_ImportTable();
            ////上傳路徑
            //// 根目錄/路徑(~/路徑)
            //String savePath = Server.MapPath(@"~\ExcelUpLoad\Sales\AMZForcast");

            //DataTable D_table = new DataTable("Excel");
            ////建立Excel欄位
            //D_table = GetExcelDefine.Dt1.Copy();
            //DataTable D_errortable = new DataTable("Error");
            ////實際顯示欄位
            //int Excel欄位數 = D_table.Columns.Count - 2;
            if ((upload_file.PostedFile != null) && (upload_file.PostedFile.ContentLength > 0))
            {
                string fileName      = System.IO.Path.GetFileName(upload_file.PostedFile.FileName);
                string LocationFiled = Server.MapPath(Str上傳路徑);
                string str頁簽名稱       = "";

                try
                {
                    DataTable D_table        = new DataTable("Excel");
                    DataTable D_errortable   = new DataTable("Error");
                    string    副檔名            = System.IO.Path.GetExtension(fileName);
                    DataTable DtColumnDefine = GetDBData("欄位定義");
                    if (Session["DataDefine"] != null)
                    {
                        Session.Remove("DataDeffine");
                    }
                    Session["DataDeffine"] = DtColumnDefine;
                    //指定Import Sheet Name
                    string  StrSheetNameCheck = "";
                    Boolean BCheck = false;
                    int     I資料起始欄, I資料起始列;

                    #region 基本資料欄位
                    //D_table.Columns.Add("預設資料");

                    #endregion

                    #region ErrorTable
                    //D_errortable.Columns.Add("SheetName");
                    //D_errortable.Columns.Add("Dept");
                    D_errortable.Columns.Add("Error");
                    #endregion
                    //
                    foreach (DataRow Dr in DtColumnDefine.Rows)
                    {
                        D_table.Columns.Add(Dr["資料名稱中文"].ToString());
                    }

                    if (DtColumnDefine.Rows.Count > 0)
                    {
                        StrSheetNameCheck = (string.IsNullOrEmpty(DtColumnDefine.Rows[0]["指定頁籤名稱"].ToString()))?"": DtColumnDefine.Rows[0]["指定頁籤名稱"].ToString();

                        if (!Boolean.TryParse(DtColumnDefine.Rows[0]["是否指定頁籤"].ToString(), out BCheck))
                        {
                            BCheck = false;
                        }

                        if (!int.TryParse(DtColumnDefine.Rows[0]["資料起始列"].ToString(), out I資料起始列))
                        {
                            I資料起始列 = 1;
                        }

                        if (!int.TryParse(DtColumnDefine.Rows[0]["資料起始欄"].ToString(), out I資料起始欄))
                        {
                            I資料起始欄 = 0;
                        }

                        while (File.Exists(LocationFiled + fileName))
                        {
                            fileName = fileName.Substring(0, fileName.Length - 副檔名.Length) + DateTime.Now.ToString("yyyyMMddhhmmssfff") + 副檔名;
                        }
                        upload_file.PostedFile.SaveAs(LocationFiled + fileName);

                        if (副檔名.ToUpper() == ".XLSX")
                        {
                            XSSFWorkbook workbook = new XSSFWorkbook(upload_file.PostedFile.InputStream);  //==只能讀取 System.IO.Stream
                            for (int x = 0; x < workbook.NumberOfSheets; x++)
                            {
                                //-- 0表示:第一個 worksheet工作表
                                XSSFSheet u_sheet = (XSSFSheet)workbook.GetSheetAt(x);
                                str頁簽名稱 = u_sheet.SheetName.ToString();
                                //檢查是否有要對應資料
                                if (BCheck && StrSheetNameCheck != str頁簽名稱)
                                {
                                    continue;
                                }
                                //-- Excel 表頭列
                                XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列);
                                IRow    DateRow   = (IRow)u_sheet.GetRow(I資料起始列);
                                //-- for迴圈的「啟始值」要加一,表示不包含 Excel表頭列
                                // for (int i = (u_sheet.FirstRowNum + 1); i <= u_sheet.LastRowNum; i++)
                                //-- 每一列做迴圈
                                //i=1第二列開始
                                for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)
                                {
                                    //--不包含 Excel表頭列的 "其他資料列"
                                    IRow row = (IRow)u_sheet.GetRow(i);

                                    #region 關鍵資料沒有不執行,避免USER亂填EXCEL
                                    string Str款號 = "";
                                    try
                                    {
                                        Str款號 = row.GetCell(1).ToString();
                                    }
                                    catch (Exception)
                                    {
                                    }
                                    if (string.IsNullOrEmpty(Str款號))
                                    {
                                        continue;
                                    }
                                    #endregion
                                    F_資料確認(ref D_table, ref D_errortable, str頁簽名稱, row, DtColumnDefine, i, I資料起始欄);
                                }
                                //-- 釋放 NPOI的資源
                                u_sheet = null;
                            }
                            //-- 釋放 NPOI的資源
                            workbook = null;
                        }
                        else
                        {
                            HSSFWorkbook workbook = new HSSFWorkbook(upload_file.PostedFile.InputStream);  //==只能讀取 System.IO.Stream
                            for (int x = 0; x < workbook.NumberOfSheets; x++)
                            {
                                HSSFSheet u_sheet   = (HSSFSheet)workbook.GetSheetAt(x); //-- 0表示:第一個 worksheet工作表
                                HSSFRow   headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列);   //-- Excel 表頭列
                                IRow      DateRow   = (IRow)u_sheet.GetRow(I資料起始列);      //-- v.1.2.4版修改
                                //檢查是否有要對應資料
                                if (BCheck && StrSheetNameCheck != str頁簽名稱)
                                {
                                    continue;
                                }
                                str頁簽名稱 = u_sheet.SheetName.ToString();
                                for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)   //-- 每一列做迴圈
                                {
                                    //--不包含 Excel表頭列的 "其他資料列"
                                    IRow row = (IRow)u_sheet.GetRow(i);

                                    #region 關鍵資料沒有不執行,避免USER亂填EXCEL
                                    string Str款號 = "";
                                    try
                                    {
                                        Str款號 = row.GetCell(1).ToString();
                                    }
                                    catch (Exception)
                                    {
                                    }
                                    if (string.IsNullOrEmpty(Str款號))
                                    {
                                        continue;
                                    }
                                    #endregion
                                    F_資料確認(ref D_table, ref D_errortable, str頁簽名稱, row, DtColumnDefine, i, I資料起始欄);
                                }
                                //-- 釋放 NPOI的資源
                                u_sheet = null;
                            }
                            //-- 釋放 NPOI的資源
                            workbook = null;
                        }
                        //--錯誤資料顯示
                        if (D_errortable.Rows.Count > 0)
                        {
                            DataView D_View3 = new DataView(D_errortable);
                            ErrorGV.DataSource = D_View3;
                            ErrorGV.DataBind();
                        }
                        if (D_table.Rows.Count > 0)
                        {
                            GridView1.DataSource = D_table;
                            GridView1.DataBind();
                            if (D_errortable.Rows.Count == 0)
                            {
                                Session["ImportExcelData"] = D_table;
                            }
                        }
                    }
                    else
                    {
                        F_ErrorShow("Please contact Mis : Import format is not defined");
                    }
                }
                catch (Exception ex)
                {
                    F_ErrorShow($"Error: {ex.Message}");
                }
            }
            else
            {
                F_ErrorShow("Please select a file to upload.");
            }
        }
Beispiel #4
0
        protected void CheckBT_Click(object sender, EventArgs e)
        {
            if ((upload_file.PostedFile != null) && (upload_file.PostedFile.ContentLength > 0))
            {
                string fileName      = System.IO.Path.GetFileName(upload_file.PostedFile.FileName);
                string LocationFiled = Server.MapPath(Str上傳路徑);
                string str頁簽名稱       = "";

                try
                {
                    DataTable D_table      = new DataTable("Excel");
                    DataTable D_errortable = new DataTable("Error");
                    DataTable DtCula       = new DataTable("CulTable");
                    string    副檔名          = System.IO.Path.GetExtension(fileName);
                    if (Session["DataDefine"] != null)
                    {
                        Session.Remove("DataDeffine");
                    }
                    int I資料起始列;

                    #region 基本資料欄位

                    #endregion

                    #region ErrorTable
                    //D_errortable.Columns.Add("SheetName");
                    //D_errortable.Columns.Add("Dept");
                    D_errortable.Columns.Add("Error");
                    #endregion
                    //
                    //foreach (DataRow Dr in DtColumnDefine.Rows)
                    //{
                    //    D_table.Columns.Add(Dr["資料名稱中文"].ToString());
                    //}

                    I資料起始列 = 0;

                    while (File.Exists(LocationFiled + fileName))
                    {
                        fileName = fileName.Substring(0, fileName.Length - 副檔名.Length) + DateTime.Now.ToString("yyyyMMddhhmmssfff") + 副檔名;
                    }
                    upload_file.PostedFile.SaveAs(LocationFiled + fileName);

                    if (副檔名.ToUpper() == ".XLSX")
                    {
                        XSSFWorkbook workbook = new XSSFWorkbook(upload_file.PostedFile.InputStream);  //==只能讀取 System.IO.Stream
                        for (int x = 0; x < workbook.NumberOfSheets; x++)
                        {
                            //-- 0表示:第一個 worksheet工作表
                            XSSFSheet u_sheet = (XSSFSheet)workbook.GetSheetAt(x);
                            str頁簽名稱 = u_sheet.SheetName.ToString();
                            //-- Excel 表頭列
                            XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列);
                            IRow    DateRow   = (IRow)u_sheet.GetRow(I資料起始列);
                            //i=1第二列開始
                            for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)
                            {
                                //--不包含 Excel表頭列的 "其他資料列"
                                IRow row = (IRow)u_sheet.GetRow(i);
                                F_資料確認(ref D_table, ref D_errortable, ref DtCula, row, i);
                            }
                            //-- 釋放 NPOI的資源
                            u_sheet = null;
                        }
                        //-- 釋放 NPOI的資源
                        workbook = null;
                    }
                    else
                    {
                        HSSFWorkbook workbook = new HSSFWorkbook(upload_file.PostedFile.InputStream);  //==只能讀取 System.IO.Stream
                        for (int x = 0; x < workbook.NumberOfSheets; x++)
                        {
                            HSSFSheet u_sheet   = (HSSFSheet)workbook.GetSheetAt(x); //-- 0表示:第一個 worksheet工作表
                            HSSFRow   headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列);   //-- Excel 表頭列
                            IRow      DateRow   = (IRow)u_sheet.GetRow(I資料起始列);      //-- v.1.2.4版修改
                            str頁簽名稱 = u_sheet.SheetName.ToString();
                            for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)       //-- 每一列做迴圈
                            {
                                //--不包含 Excel表頭列的 "其他資料列"
                                IRow row = (IRow)u_sheet.GetRow(i);

                                F_資料確認(ref D_table, ref D_errortable, ref DtCula, row, i);
                            }
                            //-- 釋放 NPOI的資源
                            u_sheet = null;
                        }
                        //-- 釋放 NPOI的資源
                        workbook = null;
                    }
                    //--錯誤資料顯示
                    if (D_errortable.Rows.Count > 0)
                    {
                        DataView D_View3 = new DataView(D_errortable);
                        ErrorGV.DataSource = D_View3;
                        ErrorGV.DataBind();
                    }
                    if (D_table.Rows.Count > 0)
                    {
                        var DistinctColor = ListColor.Distinct().ToList();
                        if (DistinctColor.Count() > 0)
                        {
                            using (DataTable dt = DtCula.Clone())
                            {
                                dt.Columns.RemoveAt(1);
                                dt.Columns[0].ColumnName = "單一顏色";
                                foreach (var item in DistinctColor)
                                {
                                    try
                                    {
                                        DataRow dr = dt.NewRow();
                                        dr[0] = item;
                                        DataView dv1 = new DataView(DtCula);
                                        DataView dv2 = new DataView(DtCula);
                                        dv1.RowFilter = " 計算color1 = '" + item + "'";
                                        dv2.RowFilter = " 計算color2 = '" + item + "'";
                                        for (int i = 2; i < dt.Columns.Count + 1; i++)
                                        {
                                            try
                                            {
                                                int x = 0;
                                                if (dv1.Count > 0)
                                                {
                                                    foreach (DataRowView dv1Row in dv1)
                                                    {
                                                        x += int.Parse(dv1Row[i].ToString());
                                                    }
                                                }
                                                if (dv2.Count > 0)
                                                {
                                                    foreach (DataRowView dv2Row in dv2)
                                                    {
                                                        x += int.Parse(dv2Row[i].ToString());
                                                    }
                                                }
                                                dr[i - 1] = x;
                                            }
                                            catch (Exception ex2)
                                            {
                                            }
                                        }
                                        dt.Rows.Add(dr);
                                    }
                                    catch (Exception ex1)
                                    {
                                    }
                                }
                                if (dt.Rows.Count > 0)
                                {
                                    GridView3.DataSource = dt;
                                    GridView3.DataBind();


                                    using (XLWorkbook wb = new XLWorkbook())
                                    {
                                        wb.Worksheets.Add(DtCula, "AMZ_PO_Qty");
                                        wb.Worksheets.Add(dt, "AMZ_Color_Qty");
                                        Response.Clear();
                                        Response.Buffer      = true;
                                        Response.Charset     = "";
                                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                        Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xlsx", "AMZ_PO"));
                                        using (MemoryStream MyMemoryStream = new MemoryStream())
                                        {
                                            wb.SaveAs(MyMemoryStream);
                                            MyMemoryStream.WriteTo(Response.OutputStream);
                                            Response.Flush();
                                            Response.End();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    F_ErrorShow($"Error: {ex.Message}");
                }
            }
        }
Beispiel #5
0
        public void XSSFTextParagraph_()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet   sheet   = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox        shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
                XSSFRichTextString rt    = new XSSFRichTextString("Test String");

                XSSFFont font  = wb.CreateFont() as XSSFFont;
                Rgb24    color = new Rgb24(0, 255, 255);
                font.SetColor(new XSSFColor(color));
                font.FontName = "Arial";
                rt.ApplyFont(font);

                shape.SetText(rt);

                List <XSSFTextParagraph> paras = shape.TextParagraphs;
                Assert.AreEqual(1, paras.Count);

                XSSFTextParagraph text = paras[(0)];
                Assert.AreEqual("Test String", text.Text);

                Assert.IsFalse(text.IsBullet);
                Assert.IsNotNull(text.GetXmlObject());
                Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
                Assert.IsNotNull(text.GetEnumerator());
                Assert.IsNotNull(text.AddLineBreak());

                Assert.IsNotNull(text.TextRuns);
                Assert.AreEqual(2, text.TextRuns.Count);
                text.AddNewTextRun();
                Assert.AreEqual(3, text.TextRuns.Count);

                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = (TextAlign.CENTER);
                Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
                text.TextAlign = (TextAlign.RIGHT);
                Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);

                text.TextFontAlign = (TextFontAlign.BASELINE);
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = (TextFontAlign.BOTTOM);
                Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);

                Assert.IsNull(text.BulletFont);
                text.BulletFont = ("Arial");
                Assert.AreEqual("Arial", text.BulletFont);

                Assert.IsNull(text.BulletCharacter);
                text.BulletCharacter = (".");
                Assert.AreEqual(".", text.BulletCharacter);

                //Assert.IsNull(text.BulletFontColor);
                Assert.AreEqual(POIUtils.Color_Empty, text.BulletFontColor);
                text.BulletFontColor = (color);
                Assert.AreEqual(color, text.BulletFontColor);

                Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);

                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (2.0);
                Assert.AreEqual(2.0, text.Indent, 0.01);
                text.Indent = (-1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (-1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);

                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (3.0);
                Assert.AreEqual(3.0, text.LeftMargin, 0.01);
                text.LeftMargin = (-1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (-1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);

                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (4.5);
                Assert.AreEqual(4.5, text.RightMargin, 0.01);
                text.RightMargin = (-1.0);
                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (-1.0);
                Assert.AreEqual(0.0, text.RightMargin, 0.01);

                Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);

                Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
                text.AddTabStop(3.14);
                Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);

                Assert.AreEqual(100.0, text.LineSpacing, 0.01);
                text.LineSpacing = (3.15);
                Assert.AreEqual(3.15, text.LineSpacing, 0.01);
                text.LineSpacing = (-2.13);
                Assert.AreEqual(-2.13, text.LineSpacing, 0.01);

                Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
                text.SpaceBefore = (3.17);
                Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
                text.SpaceBefore = (-4.7);
                Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);

                Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
                text.SpaceAfter = (6.17);
                Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
                text.SpaceAfter = (-8.17);
                Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);

                Assert.AreEqual(0, text.Level);
                text.Level = (1);
                Assert.AreEqual(1, text.Level);
                text.Level = (4);
                Assert.AreEqual(4, text.Level);

                Assert.IsTrue(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.IsBullet = (false);
                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.IsBullet = (true);
                Assert.IsTrue(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                Assert.AreEqual(0, text.BulletAutoNumberStart);
                Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);

                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
                Assert.IsTrue(text.IsBullet);
                Assert.IsTrue(text.IsBulletAutoNumber);

                //Assert.AreEqual(0, text.BulletAutoNumberStart);
                //This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
                Assert.AreEqual(1, text.BulletAutoNumberStart);


                Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
                Assert.IsTrue(text.IsBullet);
                Assert.IsTrue(text.IsBulletAutoNumber);
                Assert.AreEqual(10, text.BulletAutoNumberStart);
                Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);


                Assert.IsNotNull(text.ToString());

                new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
            }
            finally
            {
                wb.Close();
            }
        }
Beispiel #6
0
        public byte[] ExprotExcel(IList <View_客戶資訊清單> data)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet    sheet    = (XSSFSheet)workbook.CreateSheet("Export");

            XSSFCellStyle styletital = (XSSFCellStyle)workbook.CreateCellStyle();

            styletital.Alignment           = HorizontalAlignment.Center;
            styletital.BorderBottom        = BorderStyle.Thin;
            styletital.BorderLeft          = BorderStyle.Thin;
            styletital.BorderRight         = BorderStyle.Thin;
            styletital.BorderTop           = BorderStyle.Thin;
            styletital.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            styletital.FillPattern         = FillPattern.SolidForeground;

            XSSFCellStyle stylecon = (XSSFCellStyle)workbook.CreateCellStyle();

            stylecon.Alignment    = HorizontalAlignment.Left; //對齊
            stylecon.BorderBottom = BorderStyle.Thin;
            stylecon.BorderLeft   = BorderStyle.Thin;
            stylecon.BorderRight  = BorderStyle.Thin;
            stylecon.BorderTop    = BorderStyle.Thin;

            #region HeaderRow
            XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);

            headerRow.CreateCell(0).SetCellValue("客戶名稱");
            headerRow.Cells[0].CellStyle = styletital;
            headerRow.CreateCell(1).SetCellValue("客戶銀行資訊數量");
            headerRow.Cells[1].CellStyle = styletital;
            headerRow.CreateCell(2).SetCellValue("客戶聯絡人數量");
            headerRow.Cells[2].CellStyle = styletital;
            #endregion

            #region DetailRow
            int rowIndex = 1;
            foreach (var item in data)
            {
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);

                dataRow.CreateCell(0).SetCellValue(item.客戶名稱);
                dataRow.Cells[0].CellStyle = stylecon;
                dataRow.CreateCell(1).SetCellValue(item.客戶銀行資訊數量.ToString());
                dataRow.Cells[1].CellStyle = stylecon;
                dataRow.CreateCell(2).SetCellValue(item.客戶聯絡人數量.ToString());
                dataRow.Cells[2].CellStyle = stylecon;

                rowIndex++;
            }
            #endregion

            for (int i = 0; i < sheet.GetRow(0).Cells.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            // code to create workbook

            byte[] fileContents = null;
            using (var memoryStream = new MemoryStream())
            {
                workbook.Write(memoryStream);
                fileContents = memoryStream.ToArray();
            }
            return(fileContents);
        }
Beispiel #7
0
        public static void ListToSheetXlsx <T>(XSSFWorkbook workbook, List <T> list, string sheetName)
        {
            XSSFSheet     sheet     = (XSSFSheet)workbook.CreateSheet(sheetName);
            XSSFCellStyle headStyle = workbook.GetHeadStyle();

            //值类型直接返回第一列
            Type tp = typeof(T);

            //属性列表
            PropertyInfo[] properties = tp.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
            //property.Name是属性的英文名,怎么转换成中文?使用DescriptionAttribute特性
            List <string>       fieldStringArray = new List <string>();
            List <PropertyInfo> propertiesUsed   = new List <PropertyInfo>();

            foreach (var property in properties)
            {
                if (Attribute.IsDefined(property, typeof(DescriptionAttribute)))
                {
                    fieldStringArray.Add(property.GetEnumDescription());
                    propertiesUsed.Add(property);
                }
            }

            int     fieldCount = fieldStringArray.Count;
            XSSFRow headerRow  = (XSSFRow)sheet.CreateRow(0);

            headerRow.HeightInPoints = 20;
            for (int i = 0; i < fieldCount; i++)
            {
                #region 表头及样式
                headerRow.CreateCell(i).SetCellValue(fieldStringArray[i]);
                headerRow.GetCell(i).CellStyle = headStyle;
                sheet.AutoSizeColumn(i);
                #endregion
            }

            var count = list.Count();

            #region 单元格样式

            ICellStyle styleCell = workbook.CreateCellStyle();
            styleCell.Alignment         = HorizontalAlignment.Center; //居中
            styleCell.VerticalAlignment = VerticalAlignment.Center;   //垂直居中

            #endregion

            for (int i = 0; i < count; i++)
            {
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(i + 1);
                var     data    = list[i];
                for (int cellIndex = 0; cellIndex < fieldCount; cellIndex++)
                {
                    XSSFCell newCell  = (XSSFCell)dataRow.CreateCell(cellIndex, CellType.String);
                    var      property = propertiesUsed[cellIndex];
                    if (Attribute.IsDefined(property, typeof(TimeAttribute)))
                    {
                        try
                        {
                            TimeSpan      ts = new TimeSpan(0, 0, (int)property.GetValue(data));
                            StringBuilder sb = new StringBuilder();
                            if ((int)ts.TotalHours > 0)
                            {
                                sb.Append((int)ts.TotalHours + "h");
                            }
                            if (ts.Minutes > 0)
                            {
                                sb.Append(ts.Minutes + "m");
                            }
                            if (ts.Seconds > 0)
                            {
                                sb.Append(ts.Seconds + "s");
                            }

                            newCell.SetCellValue(sb.ToString());
                        }
                        catch (Exception ex)
                        {
                            ILogger logger = ServiceProviderServiceExtensions.GetRequiredService <ILogger>(
                                ServiceProviderExtension.ServiceProvider);
                            logger.LogError($"Second转换失败:" + ex.Source + Environment.NewLine + ex.StackTrace + Environment.NewLine + ex.Message + Environment.NewLine + ex.InnerException);
                            newCell.SetCellValue(property.GetValue(data).ToString());
                        }
                    }
                    else
                    {
                        var propertyValue = property.GetValue(data);
                        if (propertyValue == null)
                        {
                            newCell.SetCellValue("");
                        }
                        else
                        {
                            newCell.SetCellValue(propertyValue.ToString());
                        }
                    }

                    newCell.CellStyle = styleCell;
                }
            }
            //统一设置列宽度
            sheet.SetColumnWidth(fieldCount);
        }
Beispiel #8
0
        ///// <summary>
        ///// 清除时间
        ///// </summary>
        ///// <param name="sender"></param>
        ///// <param name="e"></param>
        //private void ClearDatePickerTime(object sender, MouseButtonEventArgs e)
        //{
        //    DatePicker dp = sender as DatePicker;
        //    dp.Text = "";
        //}

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private async void ExportExcelFile_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                string saveExcelPath = ConfigurationManager.AppSettings["Excel保存地址"];
                if (string.IsNullOrEmpty(saveExcelPath))
                {
                    saveExcelPath = System.Windows.Forms.Application.StartupPath + @"\比对记录导出";
                }
                else
                {
                    saveExcelPath += @"\比对记录导出";
                }
                await Task.Run(
                    () =>
                {
                    XSSFWorkbook workbook    = new XSSFWorkbook();
                    XSSFCellStyle style      = (XSSFCellStyle)workbook.CreateCellStyle();
                    style.Alignment          = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    style.VerticalAlignment  = NPOI.SS.UserModel.VerticalAlignment.Center;
                    style.BorderBottom       = BorderStyle.Thin;
                    style.BorderLeft         = BorderStyle.Thin;
                    style.BorderRight        = BorderStyle.Thin;
                    style.BorderTop          = BorderStyle.Thin;
                    XSSFCellStyle style2     = (XSSFCellStyle)workbook.CreateCellStyle();
                    style2.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    style2.BorderBottom      = BorderStyle.Thin;
                    style2.BorderLeft        = BorderStyle.Thin;
                    style2.BorderRight       = BorderStyle.Thin;
                    style2.BorderTop         = BorderStyle.Thin;
                    XSSFFont font            = (XSSFFont)workbook.CreateFont();
                    font.Boldweight          = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    style2.SetFont(font);
                    XSSFCellStyle style0     = (XSSFCellStyle)workbook.CreateCellStyle();
                    style0.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    style0.BorderBottom      = BorderStyle.Thin;
                    style0.BorderLeft        = BorderStyle.Thin;
                    style0.BorderRight       = BorderStyle.Thin;
                    style0.BorderTop         = BorderStyle.Thin;
                    IDataFormat dataformat   = workbook.CreateDataFormat();
                    style0.DataFormat        = dataformat.GetFormat("yyyy年MM月dd日 h:mm:ss 上午/下午");
                    XSSFSheet sheet          = (XSSFSheet)workbook.CreateSheet("比对记录");
                    int row = 0;
                    List <MyCmpFaceLogWidthImgModel> allCmpInfo = new List <MyCmpFaceLogWidthImgModel>();
                    allCmpInfo = GetCmpFaceLogByPage(1);
                    foreach (var item in allCmpInfo)
                    {
                        if (row == 0)
                        {
                            sheet.CreateRow(row).CreateCell(0).SetCellValue("序号");
                            sheet.GetRow(row).CreateCell(1).SetCellValue("抓拍通道");
                            sheet.GetRow(row).CreateCell(2).SetCellValue("抓拍时间");
                            sheet.GetRow(row).CreateCell(3).SetCellValue("模版姓名");
                            sheet.GetRow(row).CreateCell(4).SetCellValue("注册类型");
                            sheet.GetRow(row).CreateCell(5).SetCellValue("相似度");
                            sheet.GetRow(row).CreateCell(6).SetCellValue("抓拍照片");
                            sheet.GetRow(row).CreateCell(7).SetCellValue("模版照片");
                            sheet.GetRow(row).GetCell(0).CellStyle = style2;
                            sheet.GetRow(row).GetCell(1).CellStyle = style2;
                            sheet.GetRow(row).GetCell(2).CellStyle = style2;
                            sheet.GetRow(row).GetCell(3).CellStyle = style2;
                            sheet.GetRow(row).GetCell(4).CellStyle = style2;
                            sheet.GetRow(row).GetCell(5).CellStyle = style2;
                            sheet.GetRow(row).GetCell(6).CellStyle = style2;
                            sheet.GetRow(row).GetCell(7).CellStyle = style2;
                            sheet.SetColumnWidth(2, 30 * 256);
                            sheet.SetColumnWidth(6, 30 * 256);
                            sheet.SetColumnWidth(7, 30 * 256);
                            row++;
                        }
                        sheet.CreateRow(row).CreateCell(0).SetCellValue(row);
                        sheet.GetRow(row).Height = 100 * 40;
                        sheet.GetRow(row).CreateCell(1).SetCellValue(item.channelName);
                        sheet.GetRow(row).CreateCell(2).SetCellValue(item.time);
                        sheet.GetRow(row).CreateCell(3).SetCellValue(item.name);
                        sheet.GetRow(row).CreateCell(4).SetCellValue(item.type);
                        sheet.GetRow(row).CreateCell(5).SetCellValue(item.score);
                        sheet.GetRow(row).CreateCell(6).SetCellValue("");
                        sheet.GetRow(row).CreateCell(7).SetCellValue("");
                        sheet.GetRow(row).GetCell(0).CellStyle = style;
                        sheet.GetRow(row).GetCell(1).CellStyle = style;
                        sheet.GetRow(row).GetCell(2).CellStyle = style0;
                        sheet.GetRow(row).GetCell(3).CellStyle = style;
                        sheet.GetRow(row).GetCell(4).CellStyle = style;
                        sheet.GetRow(row).GetCell(5).CellStyle = style;
                        sheet.GetRow(row).GetCell(6).CellStyle = style;
                        sheet.GetRow(row).GetCell(7).CellStyle = style;
                        if (true)
                        {
                            XSSFDrawing patriarch  = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                            List <byte[]> capImage = new List <byte[]>();
                            capImage = thirft.QueryCmpLogImageH(item.ID, exportCurrDay);
                            if (capImage.Count > 0)
                            {
                                byte[] bytes            = capImage[0];
                                int pictureIdx          = workbook.AddPicture(bytes, PictureType.JPEG);
                                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 6, row, 7, row + 1);
                                XSSFPicture pict        = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                            }
                            var faceObject = thirft.QueryFaceObj(item.tcUuid);
                            if (faceObject.Count > 0 && faceObject[0].Tmplate.Count > 0)
                            {
                                byte[] bytes2            = faceObject[0].Tmplate[0].Img;
                                int pictureIdx2          = workbook.AddPicture(bytes2, PictureType.JPEG);
                                XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 7, row, 8, row + 1);
                                XSSFPicture pict2        = (XSSFPicture)patriarch.CreatePicture(anchor2, pictureIdx2);
                            }
                        }
                        row++;
                        Thread.Sleep(1);
                    }
                    FileStream fs = new FileStream(saveExcelPath + ".xlsx", FileMode.Create, FileAccess.Write);
                    workbook.Write(fs);
                    workbook = null;
                });

                MB_MODULES.Views.MyMessage.showYes("导出完成!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Beispiel #9
0
        /// <summary>
        /// 資料確認
        /// </summary>
        /// <param name="Str匯入資料Table">匯入資料表名稱</param>
        public void F_LoadData(string Str匯入資料Table)
        {
            if (XSSworkbook != null || HSSworkbook != null)
            {
                //string str頁簽名稱 = "";
                //int ISheetCheck = 0;//確認Sheet 資料是否正確,每份Sheet只會有一個對應
                try
                {
                    DTDefine = GetDBData("GetDefine", Str匯入資料Table);

                    //指定Import Sheet Name
                    string  StrSheetNameCheck = "";
                    Boolean BCheck = false;
                    int     I資料起始欄, I資料起始列;

                    DTError.Columns.Add("Error");

                    foreach (DataRow Dr in DTDefine.Rows)
                    {
                        if (Dr["IsUsing"].ToString() == "1")
                        {
                            DTImportData.Columns.Add(Dr["欄位名稱"].ToString());
                        }
                    }

                    if (DTImportData.Rows.Count > 0)
                    {
                        StrSheetNameCheck = (string.IsNullOrEmpty(DTImportData.Rows[0]["指定頁籤名稱"].ToString())) ? "" : DTImportData.Rows[0]["指定頁籤名稱"].ToString();

                        if (!bool.TryParse(DTImportData.Rows[0]["是否指定頁籤"].ToString(), out BCheck))
                        {
                            BCheck = false;
                        }

                        if (!int.TryParse(DTImportData.Rows[0]["資料起始列"].ToString(), out I資料起始列))
                        {
                            I資料起始列 = 1;
                        }

                        if (!int.TryParse(DTImportData.Rows[0]["資料起始欄"].ToString(), out I資料起始欄))
                        {
                            I資料起始欄 = 0;
                        }

                        if (Str副檔名.ToUpper() == ".XLSX")
                        {
                            for (int x = 0; x < XSSworkbook.NumberOfSheets; x++)
                            {
                                //-- 0表示:第一個 worksheet工作表
                                XSSFSheet u_sheet = (XSSFSheet)XSSworkbook.GetSheetAt(x);
                                Str匯入頁簽 = u_sheet.SheetName.ToString();

                                //檢查是否有要對應資料
                                if (BCheck && StrSheetNameCheck != Str匯入頁簽)
                                {
                                    continue;
                                }
                                //else
                                //{
                                //    ISheetCheck = +1;
                                //}
                                //-- Excel 表頭列
                                XSSFRow headerRow = (XSSFRow)u_sheet.GetRow(I資料起始列);
                                IRow    DateRow   = (IRow)u_sheet.GetRow(I資料起始列);

                                for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)
                                {
                                    //--不包含 Excel表頭列的 "其他資料列"
                                    IRow row = (IRow)u_sheet.GetRow(i);
                                    F_資料確認(row, i, I資料起始欄);
                                }
                                //-- 釋放 NPOI的資源
                                u_sheet = null;
                            }
                        }
                        else
                        {
                            for (int x = 0; x < HSSworkbook.NumberOfSheets; x++)
                            {
                                HSSFSheet u_sheet   = (HSSFSheet)HSSworkbook.GetSheetAt(x); //-- 0表示:第一個 worksheet工作表
                                HSSFRow   headerRow = (HSSFRow)u_sheet.GetRow(I資料起始列);      //-- Excel 表頭列
                                IRow      DateRow   = (IRow)u_sheet.GetRow(I資料起始列);         //-- v.1.2.4版修改
                                                                                            //檢查是否有要對應資料
                                if (BCheck && StrSheetNameCheck != Str匯入頁簽)
                                {
                                    continue;
                                }
                                //else
                                //{
                                //    ISheetCheck = +1;
                                //}
                                Str匯入頁簽 = u_sheet.SheetName.ToString();
                                for (int i = I資料起始列; i <= u_sheet.LastRowNum; i++)   //-- 每一列做迴圈
                                {
                                    //--不包含 Excel表頭列的 "其他資料列"
                                    IRow row = (IRow)u_sheet.GetRow(i);
                                    F_資料確認(row, i, I資料起始欄);
                                }
                                //-- 釋放 NPOI的資源
                                u_sheet = null;
                            }
                        }
                        //--錯誤資料顯示
                        DataView D_View3 = new DataView(DTError);

                        if (DTError.Rows.Count > 0)
                        {
                            //if (str頁簽名稱 == "AMZCapacity")
                            //{
                            //    //CapacityErrorGV.DataSource = D_View3;
                            //    //CapacityErrorGV.DataBind();
                            //}
                            //else
                            //{
                            //    //GuidanceErrorGV.DataSource = D_View3;
                            //    //GuidanceErrorGV.DataBind();
                            //}
                            //if (ISheetCheck != 1)
                            //{
                            //    //F_ErrorShow(str頁簽名稱 + "Sheet 比對失敗");
                            //    StrErrorShow = Str頁簽名稱 + "Sheet 比對失敗";
                            //}
                        }
                        if (this.DTImportData.Rows.Count > 0)
                        {
                            if (DTError.Rows.Count == 0)
                            {
                                //Session[StrExcelSheet] = this.DTImportData;
                            }
                        }
                    }
                    else
                    {
                        StrErrorShow = "Please contact Mis : Import format is not defined";
                    }
                }
                catch (Exception ex)
                {
                    StrErrorShow = $"Error: {ex.Message}";
                }
            }
            else
            {
                StrErrorShow = "Data check fail";
            }
        }
Beispiel #10
0
 public XSSFRowShifter(XSSFSheet sh)
 {
     sheet = sh;
 }
Beispiel #11
0
        public static MemoryStream CreateExcelHeader(MDataTable header, Dictionary <string, string[]> validateData)
        {
            MemoryStream ms = new MemoryStream();

            if (header != null && header.Rows.Count > 0)
            {
                MDataTable importHeader = header.FindAll("Import=1");
                try
                {
                    XSSFWorkbook export = new XSSFWorkbook();
                    ICellStyle   style  = GetStyle(export, HSSFColor.LightOrange.Index);
                    ISheet       sheet  = export.CreateSheet("Sheet1");//创建内存Excel
                    #region 创建引用
                    int rowStartIndex = 1;
                    CreateValidationSheet(export, validateData, rowStartIndex);
                    #endregion
                    importHeader.Rows.Sort("ORDER BY MergeIndexed DESC");//Hidden=0 AND (Export=1 OR Field LIKE 'mg_%')
                    MDataTable headTable               = importHeader.Clone();
                    int        ColTitleRowCount        = 0;
                    Dictionary <string, int> formatdic = new Dictionary <string, int>();
                    for (int i = importHeader.Rows.Count - 1; i >= 0; i--)//MDataTable 不支持 NOT LIKE
                    {
                        if (importHeader.Rows[i]["Field"].Value.ToString().IndexOf("mg") > -1)
                        {
                            importHeader.Rows.RemoveAt(i);//非字段列移除
                        }
                    }
                    int colSum = importHeader.Rows.Count;//实际列数
                    importHeader.Rows.Sort("ORDER BY OrderNum ASC");
                    if (!ExportMulHeader(header, true))
                    {
                        IRow  row = sheet.CreateRow(0);
                        ICell cell;
                        for (int i = 0; i < colSum; i++)
                        {
                            string title = importHeader.Rows[i]["Title"].Value.ToString();
                            cell = row.CreateCell(i);
                            cell.SetCellValue(title);//设置列头
                            sheet.SetColumnWidth(i, 3000);
                            cell.CellStyle = style;
                        }
                    }
                    else
                    {
                        CreateMulHeadExcel(export, headTable, out ColTitleRowCount, colSum);
                        ColTitleRowCount -= 1;
                    }
                    for (int i = 0; i < importHeader.Rows.Count; i++)
                    {
                        string formater = importHeader.Rows[i].Get <string>("Formatter");
                        if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
                        {
                            formatdic.Add(formater, i);//存储列索引
                        }
                    }

                    int       maxRow    = 50000;//限制最大行数(07之前版本的excel最大行数为65536,但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
                    XSSFSheet xssfSheet = (XSSFSheet)sheet;
                    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);

                    for (int i = 0; i < importHeader.Rows.Count; i++)
                    {
                        MDataRow dtRow = importHeader.Rows[i];

                        string formatter = dtRow.Get <string>("Formatter");

                        if (formatter == "boolFormatter")
                        {
                            formatter = "#是否";                                                                                             //对bool型特殊处理。
                        }
                        if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
                        {
                            //处理数据的有效性
                            CellRangeAddressList      regions      = null;
                            IDataValidationConstraint constraint   = null;
                            IDataValidation           dataValidate = null;
                            //int maxRow = 65535;
                            if (validateData.ContainsKey(formatter))
                            {
                                regions = new CellRangeAddressList(ColTitleRowCount + 1, maxRow, i, i);
                                string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");

                                /*03版本api
                                 * constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
                                 * dataValidate = new HSSFDataValidation(regions, constraint);
                                 */
                                constraint   = dvHelper.CreateFormulaListConstraint(key);
                                dataValidate = dvHelper.CreateValidation(constraint, regions);
                                sheet.AddValidationData(dataValidate);

                                //regions = new CellRangeAddressList(ColTitleRowCount, maxRow, i, i);
                                //string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
                                //constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
                                //dataValidate = new HSSFDataValidation(regions, constraint);
                                //sheet.AddValidationData(dataValidate);
                            }
                            //
                            if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
                            {
                                string Parentformatter = formatter;
                                while (formatdic.ContainsKey(Parentformatter))
                                {
                                    int point       = 0;
                                    int parentindex = formatdic[Parentformatter];
                                    formatdic.Remove(Parentformatter);
                                    foreach (var item in formatdic)
                                    {
                                        if (item.Key.IndexOf('=') > -1)
                                        {
                                            string parent = item.Key.Split('=')[1];
                                            parent = parent.Replace(">", "#");
                                            if (parent.Equals(Parentformatter.Split('=')[0]))
                                            {
                                                int selfindex = item.Value;
                                                //int parentindex = formatdic[hereformatter];
                                                string t = IntToMoreChar(parentindex);
                                                for (int im = ColTitleRowCount; im < maxRow; im++)
                                                {
                                                    string func = string.Format("@INDIRECT({0}{1})", t, im + 1);
                                                    regions      = new CellRangeAddressList(im, im, selfindex, selfindex);
                                                    constraint   = dvHelper.CreateFormulaListConstraint(func);
                                                    dataValidate = dvHelper.CreateValidation(constraint, regions);
                                                    sheet.AddValidationData(dataValidate);
                                                }
                                                //for (int im = ColTitleRowCount; im < maxRow; im++)//1000应为maxRow
                                                //{
                                                //    string func = "INDIRECT(" + t + (im + 1) + ")";//excel2013不能级联,03可以,其他没测过
                                                //    regions = new CellRangeAddressList(ColTitleRowCount, im, selfindex, selfindex);
                                                //    constraint = DVConstraint.CreateFormulaListConstraint(func);
                                                //    dataValidate = new HSSFDataValidation(regions, constraint);
                                                //    sheet.AddValidationData(dataValidate);
                                                //}
                                                Parentformatter = item.Key;
                                                break;
                                            }
                                        }
                                        point += 1;
                                    }
                                    if (point.Equals(formatdic.Count))
                                    {
                                        Parentformatter = string.Empty;
                                    }
                                }
                            }
                        }
                    }
                    export.Write(ms);
                    ms.Flush();
                    ms.Close();
                }
                catch (Exception err)
                {
                    Log.WriteLogToTxt(err);
                }
            }
            return(ms);
        }
Beispiel #12
0
        /**
         * Parse a given excel file containing interested addresses data
         * and extract needfull data (complete address, territory number
         * and appartment information). A specific row formatting is applied
         * depending on the rows contents.
         *
         * param name="sourceFile" is the location of the file to parse.
         */
        public void ParseTerritoryHelperInterestedFile(String sourceFile)
        {
            XSSFWorkbook wb;

            // Reading some configuration/initialization data...
            Console.WriteLine("Reading some configuration/initialization data...");
            string excelFileLocation       = System.Configuration.ConfigurationManager.AppSettings["sourceFilePath"];
            string lastDataColumnsCell     = System.Configuration.ConfigurationManager.AppSettings["excel-last-data-column"];
            string wantedColumnsListString = System.Configuration.ConfigurationManager.AppSettings["excel-wanted-columns"];


            // If the default source file location should be overwritten...
            if (sourceFile != null)
            {
                // Overwrite the default source file location
                excelFileLocation = sourceFile;
            }

            // Wait until anexcel addresses file is available at the location defined by 'excelFileLocation'
            int count = 0;

            while (!File.Exists(excelFileLocation) || IsFileLocked(excelFileLocation))
            {
                if (count == 0)
                {
                    Console.WriteLine("Wait until an excel addresses file is available at " + excelFileLocation + "...");
                }
                count++;
            }

            using (FileStream file = new FileStream(@excelFileLocation, FileMode.Open, FileAccess.Read))
            {
                wb = new XSSFWorkbook(file);
            }

            XSSFSheet sh       = (XSSFSheet)wb.GetSheetAt(0);
            int       rowCount = sh.LastRowNum + 1;

            Console.WriteLine("Source file row count = " + rowCount);
            //Console.WriteLine("Source file columns count = " + sh.GetColumnHelper().lastDataColumnsCell);

            SetSheetSpecialConditonalFormatting(sh);

            Console.WriteLine("Setting columns auto filters...");
            // Set the columns auto filters
            sh.SetAutoFilter(CellRangeAddress.ValueOf("A1" + ":" + lastDataColumnsCell + rowCount));

            // Get the list of column which should remain in the resulting output.
            List <int> wantedColumnsList = wantedColumnsListString.Split(',').Select(int.Parse).ToList();
            int        columnCount       = AlphabetOrdinal(lastDataColumnsCell);

            Console.WriteLine("Hiding unnecessary columns...");
            // Hide all not wanted columns
            for (int i = 0; i < columnCount; i++)
            {
                if (!wantedColumnsList.Contains(i))
                {
                    sh.SetColumnHidden(i, true);
                }
            }

            // Freeze first row ( headers )
            sh.CreateFreezePane(0, 1);


            string fileLocation = System.Configuration.ConfigurationManager.AppSettings["destinationFilePath"];

            //string fileLocation = "C:\\Development\\c-sharp\\ExcelNPOI\\adresses-bielefeld.xlsx";
            Console.WriteLine("Writing Resulting Excel File to disk: " + fileLocation);

            using (var stream = new FileStream(fileLocation, FileMode.Create, FileAccess.Write))
            {
                wb.Write(stream);
            }

            //Console.WriteLine("Opening the resulting processed file...");
            //Process.Start(fileLocation);
        }
Beispiel #13
0
        /// <summary>
        ///    DataSet 을 Excel 파일로 저장한다.
        ///    [ NPOI 라이브러리를 이용한 컨버팅 작업 진행중 ]
        /// </summary>
        /// <param name="FileName">
        ///    Excel File 명 PullPath
        /// </param>
        /// <param name="DS">
        ///    Excel 로 저장할 대상 DataSet 객체.
        /// </param>
        /// <param name="ExistDel">
        ///    동일한 파일명이 있을 때 삭제 할 것인지 여부, 파일이 있고 false 면 저장안하고 그냥 false 를 리턴.
        /// </param>
        /// <param name="OldExcel">
        ///    xls 형태로 저장할 것인지 여부, false 이면 xlsx 형태로 저장함.
        /// </param>

        private static bool SaveExcel(string FileName, DataSet DS, bool ExistDel, bool OldExcel)
        {
            bool result = true;

            if (File.Exists(FileName))
            {
                if (ExistDel)
                {
                    File.Delete(FileName);
                }
                else
                {
                    return(result);
                }
            }

            string TempFile = FileName;

            // 파일 확장자가 xls 이나 xlsx 가 아니면 아예 파일을 안만들어서
            // 템프파일로 생성후 지정한 파일명으로 변경..

            try
            {
                XSSFWorkbook WB = new XSSFWorkbook();

                foreach (DataTable DT in DS.Tables)
                {
                    XSSFSheet WS = WB.CreateSheet(DT.TableName) as XSSFSheet;
                    XSSFRow   HR = WS.CreateRow(0) as XSSFRow;

                    XSSFCellStyle HeaderStyle = WB.CreateCellStyle() as XSSFCellStyle;
                    HeaderStyle.Alignment         = HorizontalAlignment.Center;
                    HeaderStyle.VerticalAlignment = VerticalAlignment.Center;

                    XSSFFont HeaderFont = WB.CreateFont() as XSSFFont;
                    HeaderFont.FontHeightInPoints = 11;
                    HeaderFont.FontName           = "맑은 고딕";
                    HeaderFont.Boldweight         = (short)FontBoldWeight.Bold;

                    HeaderStyle.SetFont(HeaderFont);

                    int[] ColumnWidths = new int[] { 9, 12, 8, 8, 45, 45, 14 };

                    // 엑셀의 헤더 부분(DataTable의 Columns 기록) 정의 및 출력

                    for (int i = 0; i < DT.Columns.Count; i++)
                    {
                        XSSFCell HC = HR.CreateCell(i) as XSSFCell;

                        HC.SetCellValue(DT.Columns[i].ColumnName);
                        HC.CellStyle = HeaderStyle;

                        WS.SetColumnWidth(i, (int)((ColumnWidths[i] + 0.72) * 256));
                    }

                    XSSFCellStyle BodyStyle = WB.CreateCellStyle() as XSSFCellStyle;
                    BodyStyle.Alignment         = HorizontalAlignment.Center;
                    BodyStyle.VerticalAlignment = VerticalAlignment.Center;
                    BodyStyle.WrapText          = true;

                    XSSFFont BodyFont = WB.CreateFont() as XSSFFont;
                    BodyFont.FontHeightInPoints = 11;
                    BodyFont.FontName           = "맑은 고딕";
                    BodyFont.Boldweight         = (short)FontBoldWeight.None;

                    BodyStyle.SetFont(BodyFont);

                    // 엑셀의 바디 부분(Datable의 Rows 기록) 정의 및 출력

                    int RowCount = 0;

                    foreach (DataRow DR in DT.Rows)
                    {
                        XSSFRow BR = WS.CreateRow(++RowCount) as XSSFRow;
                        BR.HeightInPoints = 28 * Math.Max(DR["상점 내역"].ToString().Split('\n').Length, DR["벌점 내역"].ToString().Split('\n').Length);

                        int ColumnCount = 0;

                        foreach (DataColumn DC in DT.Columns)
                        {
                            XSSFCell BC = BR.CreateCell(ColumnCount++) as XSSFCell;

                            BC.SetCellValue(DR[DC.ColumnName] as string);
                            BC.CellStyle = BodyStyle;
                        }
                    }
                }

                using (FileStream FS = new FileStream(TempFile, FileMode.Create, FileAccess.Write))
                {
                    WB.Write(FS);
                }
            }
            catch (Exception)
            {
                result = false;
            }

            return(result);
        }