Ejemplo n.º 1
0
        public static void Run()
        {
            // Instantiate the workbook object
            Workbook workbook = new Workbook(sourceDir + "sampleFindingCellsWithStringOrNumber.xlsx");

            workbook.CalculateFormula();

            // Get Cells collection
            Cells cells = workbook.Worksheets[0].Cells;

            FindOptions opts = new FindOptions();

            opts.LookInType = LookInType.Values;
            opts.LookAtType = LookAtType.EntireContent;

            // Find the cell with the input integer or double
            Cell cell1 = cells.Find(224, null, opts);

            if (cell1 != null)
            {
                Console.WriteLine("Name of the cell containing the value: " + cell1.Name);
            }
            else
            {
                Console.WriteLine("Record not found ");
            }

            // Find the cell with the input string
            Aspose.Cells.Cell cell2 = cells.Find("Items E", null, opts);

            if (cell2 != null)
            {
                Console.WriteLine("Name of the cell containing the value: " + cell2.Name);
            }
            else
            {
                Console.WriteLine("Record not found ");
            }

            // Find the cell containing with the input string
            opts.LookAtType = LookAtType.Contains;
            Cell cell3 = cells.Find("Data", null, opts);

            if (cell3 != null)
            {
                Console.WriteLine("Name of the cell containing the value: " + cell3.Name);
            }
            else
            {
                Console.WriteLine("Record not found ");
            }

            Console.WriteLine("FindingCellsWithStringOrNumber executed successfully.");
        }
Ejemplo n.º 2
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            //Instantiate the workbook object
            Workbook workbook = new Workbook(dataDir + "book1.xls");

            //Get Cells collection
            Cells cells = workbook.Worksheets[0].Cells;

            FindOptions opts = new FindOptions();

            opts.LookInType = LookInType.Values;
            opts.LookAtType = LookAtType.EntireContent;

            //Find the cell with the input integer or double
            Cell cell1 = cells.Find(205, null, opts);

            if (cell1 != null)
            {
                Console.WriteLine("Name of the cell containing the value: " + cell1.Name);
            }
            else
            {
                Console.WriteLine("Record not found ");
            }

            //Find the cell with the input string
            Aspose.Cells.Cell cell2 = cells.Find("Items A", null, opts);

            if (cell2 != null)
            {
                Console.WriteLine("Name of the cell containing the value: " + cell2.Name);
            }
            else
            {
                Console.WriteLine("Record not found ");
            }

            //Find the cell containing with the input string
            opts.LookAtType = LookAtType.Contains;
            Cell cell3 = cells.Find("Data", null, opts);

            if (cell3 != null)
            {
                Console.WriteLine("Name of the cell containing the value: " + cell3.Name);
            }
            else
            {
                Console.WriteLine("Record not found ");
            }
        }
Ejemplo n.º 3
0
        public static void Main(string[] args)
        {
            //ExStart:1
            // The path to the documents directory.
            string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            //Instantiate a Workbook.
            Workbook workbook = new Workbook();

            //Get cells collection in the first (default) worksheet.
            Cells cells = workbook.Worksheets[0].Cells;

            //Get the D3 cell.
            Aspose.Cells.Cell c = cells["D3"];

            //Get the style of the cell.
            Style s = c.GetStyle();

            //Set foreground color for the cell from the default theme Accent2 color.
            s.ForegroundThemeColor = new ThemeColor(ThemeColorType.Accent2, 0.5);

            //Set the pattern type.
            s.Pattern = BackgroundType.Solid;

            //Get the font for the style.
            Aspose.Cells.Font f = s.Font;

            //Set the theme color.
            f.ThemeColor = new ThemeColor(ThemeColorType.Accent4, 0.1);

            //Apply style.
            c.SetStyle(s);

            //Put a value.
            c.PutValue("Testing1");

            //Save the excel file.
            workbook.Save(dataDir + "output.out.xlsx");
            //ExEnd:1
        }
Ejemplo n.º 4
0
        private Aspose.Cells.Cell WriteCell(int irow, int icol, object obj, int dq)
        {
            Aspose.Cells.Cell cl = sheet.Cells[irow, icol];
            cl.PutValue(obj);
            switch (dq)
            {
            case 1:
                cl.Style.HorizontalAlignment = TextAlignmentType.Center;
                break;

            case 2:
                cl.Style.HorizontalAlignment = TextAlignmentType.Left;
                break;
            }
            cl.Style = st;
            return(cl);
        }
        public Aspose.Cells.Workbook GetExcel()
        {
            // Instantiate a Workbook object that represents Excel file.
            Workbook wb = new Workbook();

            // When you create a new workbook, a default "Sheet1" is added to the workbook.
            Worksheet sheet = wb.Worksheets[0];

            // Access the "A1" cell in the sheet.
            Aspose.Cells.Cell cell = sheet.Cells["A1"];

            // Input the "Hello World!" text into the "A1" cell
            cell.PutValue("Hello World!");

            // Save the Excel file.
            return(wb);
        }
Ejemplo n.º 6
0
        static void Main(string[] args)
        {
            //Instantiate a new Workbook object.
            Workbook workbook = new Workbook();
            //Get the First sheet.
            Worksheet worksheet = workbook.Worksheets[0];

            //Define A1 Cell.
            Aspose.Cells.Cell cell = worksheet.Cells["A1"];
            //Add a hyperlink to it.
            int index = worksheet.Hyperlinks.Add("A1", 1, 1, "http://www.aspose.com/");
            worksheet.Hyperlinks[index].TextToDisplay = "Aspose Site!";
            worksheet.Hyperlinks[index].ScreenTip = "Click to go to Aspose site";

            //Save the excel file.
            workbook.Save("Hyperlink_test.xls");
        }
Ejemplo n.º 7
0
        public CellValue checkData(int v_col, EXCEL.Cell v_cellData)
        {
            ExcelHeaderDecorate ehd = m_header_dct[v_col];
            string             type = ehd.Type;
            ECellDataStructure ds   = ehd.DataStructure;
            CellValue          val  = null;
            object             data = v_cellData.Value;
            bool success            = false;

            if (data == null || string.IsNullOrEmpty(data.ToString()))
            {
                if (!ehd.CanBeEmpty)
                {
                    Debug.Exception("不可为空");
                    return(new MissVal());
                }
            }
            switch (ds)
            {
            case ECellDataStructure.single:
                val     = CellValue.CheckCellVal(ehd);
                success = val.Init(v_cellData, ehd.get_default(), ehd.get_constraint());
                break;

            case ECellDataStructure.array:
                val = new ListVal(type);
                //val.FieldType = type;
                val.IsStretch = ehd.IsStretch;
                success       = val.Init(v_cellData, ehd.get_default(), ehd.get_constraint());
                break;
            }

            if (success)
            {
                val.IsStretch = ehd.IsStretch;
                return(val);
            }
            else
            {
                val     = new MissVal();
                success = val.Init("[nil]");
                Debug.Exception("没有找到名为{0}类型为{1}的ID", v_cellData.Value, type);
                return(null);
            }
        }
Ejemplo n.º 8
0
    //Quang Huy 2014-02-19
    /// <summary>
    /// Tạo viền cho ô
    /// </summary>
    /// <param name="cell"></param>
    /// <param name="position"></param>
    /// <param name="color"></param>
    private bool SetCellBorder(Aspose.Cells.Cell cell, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
    {
        try
        {
            System.Drawing.Color c     = color ?? System.Drawing.Color.Black;
            Aspose.Cells.Style   style = cell.GetStyle();

            if (((int)position & (int)BorderPosition.Top) == (int)BorderPosition.Top)
            {
                style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.TopBorder].Color     = c;
            }
            if (((int)position & (int)BorderPosition.Right) == (int)BorderPosition.Right)
            {
                style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.RightBorder].Color     = c;
            }
            if (((int)position & (int)BorderPosition.Bottom) == (int)BorderPosition.Bottom)
            {
                style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.BottomBorder].Color     = c;
            }
            if (((int)position & (int)BorderPosition.Left) == (int)BorderPosition.Left)
            {
                style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.LeftBorder].Color     = c;
            }
            if (((int)position & (int)BorderPosition.DiagonalDown) == (int)BorderPosition.DiagonalDown)
            {
                style.Borders[Aspose.Cells.BorderType.DiagonalDown].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.DiagonalDown].Color     = c;
            }
            if ((((int)position & (int)BorderPosition.DiagonalUp) == (int)BorderPosition.DiagonalUp) || (position == BorderPosition.Full))
            {
                style.Borders[Aspose.Cells.BorderType.DiagonalUp].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.DiagonalUp].Color     = c;
            }

            //Apply the border styles to the cell
            cell.SetStyle(style);
            return(true);
        }
        catch { }
        return(false);
    }
Ejemplo n.º 9
0
        public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            // Instantiating a Workbook object
            Workbook workbook = new Workbook();

            // Clearing all the worksheets
            workbook.Worksheets.Clear();

            // Adding a new worksheet to the Excel object
            int i = workbook.Worksheets.Add();

            // Obtaining the reference of the newly added worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[i];

            // Accessing the "A1" cell from the worksheet
            Aspose.Cells.Cell cell = worksheet.Cells["A1"];

            // Adding some value to the "A1" cell
            cell.PutValue("Visit Aspose!");

            // Setting the horizontal alignment of the text in the "A1" cell
            Style style = cell.GetStyle();

            // Setting the vertical alignment of the text in a cell
            style.VerticalAlignment = TextAlignmentType.Center;

            cell.SetStyle(style);

            // Saving the Excel file
            workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
            // ExEnd:1
        }
Ejemplo n.º 10
0
        public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            // Instantiating a Workbook object
            Workbook workbook = new Workbook();

            // Adding a new worksheet to the Excel object
            int i = workbook.Worksheets.Add();

            // Obtaining the reference of the newly added worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[i];

            // Accessing the "A1" cell from the worksheet
            Aspose.Cells.Cell cell = worksheet.Cells["A1"];

            // Adding some value to the "A1" cell
            cell.PutValue("Hello Aspose!");

            // Obtaining the style of the cell
            Style style = cell.GetStyle();

            // ExStart:SetSubscript
            // Setting subscript effect
            style.Font.IsSubscript = true;
            // ExEnd:SetSubscript

            // Applying the style to the cell
            cell.SetStyle(style);

            // Saving the Excel file
            workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
            // ExEnd:1
        }
Ejemplo n.º 11
0
        static void Main(string[] args)
        {
            // Tracing Precedents

            //Instantiating a Workbook object
            Workbook workbook = new Workbook("C:\\book1.xls");
            Cells    cells    = workbook.Worksheets[0].Cells;

            Aspose.Cells.Cell cell = cells["B7"];

            //Tracing precedents of the cell B7.
            //The return array contains ranges and cells.
            ReferredAreaCollection ret = cell.GetPrecedents();

            //Printing all the precedent cells' name.
            if (ret != null)
            {
                for (int m = 0; m < ret.Count; m++)
                {
                    ReferredArea  area          = ret[m];
                    StringBuilder stringBuilder = new StringBuilder();
                    if (area.IsExternalLink)
                    {
                        stringBuilder.Append("[");
                        stringBuilder.Append(area.ExternalFileName);
                        stringBuilder.Append("]");
                    }
                    stringBuilder.Append(area.SheetName);
                    stringBuilder.Append("!");
                    stringBuilder.Append(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
                    if (area.IsArea)
                    {
                        stringBuilder.Append(":");
                        stringBuilder.Append(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
                    }


                    Console.WriteLine(stringBuilder.ToString());
                }
            }
        }
Ejemplo n.º 12
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            //Instantiating a Workbook object
            Workbook workbook = new Workbook();

            //Adding a new worksheet to the Excel object
            int i = workbook.Worksheets.Add();

            //Obtaining the reference of the newly added worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[i];

            //Accessing the "A1" cell from the worksheet
            Aspose.Cells.Cell cell = worksheet.Cells["A1"];

            //Adding some value to the "A1" cell
            cell.PutValue("Hello Aspose!");

            //Obtaining the style of the cell
            Style style = cell.GetStyle();

            //Setting the font to be underlined
            style.Font.Underline = FontUnderlineType.Single;

            //Applying the style to the cell
            cell.SetStyle(style);

            //Saving the Excel file
            workbook.Save(dataDir + "book1.xls", SaveFormat.Excel97To2003);
        }
Ejemplo n.º 13
0
        public static void Main(string[] args)
        {
            //ExStart:1
            // The path to the documents directory.
            string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            //Instantiating a Workbook object
            Workbook workbook = new Workbook();

            //Obtaining the reference of the worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Accessing the "A1" cell from the worksheet
            Aspose.Cells.Cell cell = worksheet.Cells["A1"];

            //Adding some value to the "A1" cell
            cell.PutValue("Visit Aspose!");

            //Setting the horizontal alignment of the text in the "A1" cell
            Style style = cell.GetStyle();

            //Setting the indentation level of the text (inside the cell) to 2
            style.IndentLevel = 2;

            cell.SetStyle(style);

            //Saving the Excel file
            workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
            //ExEnd:1
        }
Ejemplo n.º 14
0
        public virtual bool Init(EXCEL.Cell v_cellData, string v_default = null, string[] v_constraint = null)
        {
            string strVal;

            if (v_cellData.Value == null || string.IsNullOrEmpty(v_cellData.StringValue))
            {
                if (!string.IsNullOrEmpty(v_default))
                {
                    strVal = v_default;
                }
                else
                {
                    _isMiss = true;
                    return(true);
                }
            }
            else
            {
                strVal = v_cellData.Value.ToString();
            }

            return(Init(strVal, v_constraint));
        }
Ejemplo n.º 15
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            //Instantiating a Workbook object
            Workbook workbook = new Workbook();

            //Obtaining the reference of the worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Accessing the "A1" cell from the worksheet
            Aspose.Cells.Cell cell = worksheet.Cells["A1"];

            //Adding some value to the "A1" cell
            cell.PutValue("Visit Aspose!");

            //Setting the horizontal alignment of the text in the "A1" cell
            Style style = cell.GetStyle();

            //Setting the rotation of the text (inside the cell) to 25
            style.RotationAngle = 25;

            cell.SetStyle(style);

            //Saving the Excel file
            workbook.Save(dataDir + "book1.xls", SaveFormat.Excel97To2003);
        }
Ejemplo n.º 16
0
 //Quang Huy 2014-02-19
 /// <summary>
 /// Thực hiện set value cho cell
 /// </summary>
 /// <param name="cellName">Tên của ô (VD "B6")</param>
 /// <param name="value">Giá trị</param>
 public bool WriteToCell(string cellName, object value)
 {
     Aspose.Cells.Cell cell = _worksheet.Cells[cellName];
     return(WriteToCell(cell.Row, cell.Column, value));
 }
Ejemplo n.º 17
0
 //Quang Huy 2014-02-19
 /// <summary>
 /// Thực hiện đặt viền cho ô
 /// </summary>
 /// <param name="rowIndex">chỉ số dòng (dòng 1 chỉ số là 0)</param>
 /// <param name="colIndex">chỉ số cột (cột A chỉ số là 1)</param>
 /// <param name="position">Vị trí viền (mặc định viền xung quanh)</param>
 /// <param name="color">màu viền (mặc định đen)</param>
 /// <returns></returns>
 public bool SetCellBorder(int rowIndex, int colIndex, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
 {
     Aspose.Cells.Cell cell = _worksheet.Cells[rowIndex, colIndex];
     return(SetCellBorder(cell, position, color));
 }
Ejemplo n.º 18
0
 /// <summary>
 /// FreezePanes
 /// </summary>
 /// <param name="cellName">Tên cột</param>
 /// <param name="freezedRow"></param>
 /// <param name="freezedCol"></param>
 public void FreezePanes(string cellName)
 {
     Aspose.Cells.Cell cell = _worksheet.Cells[cellName];
     _worksheet.FreezePanes(cellName, cell.Row, cell.Column);
 }
Ejemplo n.º 19
0
        public static void Main(string[] args)
        {
            //ExStart:1
            // The path to the documents directory.
            string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            //Instantiate a new workbook
            Workbook workbook = new Workbook();
            //Get the cells in the first(default) worksheet
            Cells cells = workbook.Worksheets[0].Cells;

            //Get the A1 cell
            Aspose.Cells.Cell p = cells["A1"];
            //Enter a value
            p.PutValue("Preface");
            //Get the A10 cell
            Aspose.Cells.Cell A = cells["A10"];
            //Enter a value.
            A.PutValue("page1");
            //Get the H15 cell
            Aspose.Cells.Cell D = cells["H15"];
            //Enter a value
            D.PutValue("page1(H15)");
            //Add a new worksheet to the workbook
            workbook.Worksheets.Add();
            //Get the cells in the second sheet
            cells = workbook.Worksheets[1].Cells;
            //Get the B10 cell in the second sheet
            Aspose.Cells.Cell B = cells["B10"];
            //Enter a value
            B.PutValue("page2");
            //Add a new worksheet to the workbook
            workbook.Worksheets.Add();
            //Get the cells in the third sheet
            cells = workbook.Worksheets[2].Cells;
            //Get the C10 cell in the third sheet
            Aspose.Cells.Cell C = cells["C10"];
            //Enter a value
            C.PutValue("page3");

            //Create a main PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry pbeRoot = new Aspose.Cells.Rendering.PdfBookmarkEntry();
            //Specify its text
            pbeRoot.Text = "Sections";
            //Set the destination cell/location
            pbeRoot.Destination = p;

            //Set its sub entry array list
            pbeRoot.SubEntry = new ArrayList();

            //Create a sub PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry subPbe1 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
            //Specify its text
            subPbe1.Text = "Section 1";
            //Set its destination cell
            subPbe1.Destination = A;
            //Define/Create a sub Bookmark entry object of "Section A"
            Aspose.Cells.Rendering.PdfBookmarkEntry ssubPbe = new Aspose.Cells.Rendering.PdfBookmarkEntry();
            //Specify its text
            ssubPbe.Text = "Section 1.1";
            //Set its destination
            ssubPbe.Destination = D;
            //Create/Set its sub entry array list object
            subPbe1.SubEntry = new ArrayList();
            //Add the object to "Section 1"
            subPbe1.SubEntry.Add(ssubPbe);
            //Add the object to the main PDF root object
            pbeRoot.SubEntry.Add(subPbe1);

            //Create a sub PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry subPbe2 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
            //Specify its text
            subPbe2.Text = "Section 2";
            //Set its destination
            subPbe2.Destination = B;
            //Add the object to the main PDF root object
            pbeRoot.SubEntry.Add(subPbe2);

            //Create a sub PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry subPbe3 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
            //Specify its text
            subPbe3.Text = "Section 3";
            //Set its destination
            subPbe3.Destination = C;
            //Add the object to the main PDF root object
            pbeRoot.SubEntry.Add(subPbe3);

            //Create an instance of PdfSaveOptions
            Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();

            //Set the PDF Bookmark root object
            pdfSaveOptions.Bookmark = pbeRoot;

            //Save the pdf file
            workbook.Save(dataDir + "PDFBookmarks_test.out.pdf", pdfSaveOptions);
            //ExEnd:1
        }
Ejemplo n.º 20
0
        public ActionResult Export(string queryJson)
        {
            Pagination pagination = new Pagination();

            pagination.page          = 1;
            pagination.rows          = 100000000;
            pagination.p_kid         = "rownum idx";
            pagination.p_fields      = @"equipmentname,purpose,
(Concat(equipone,equipunitone)) as equipone,
equipratioone,
 (Concat(practicalequipone,practicalequipunitone)) as practicalequipone,
(Concat(equiptwo,equipunittwo)) as equiptwo,
equipratiotwo,
(Concat(practicalequiptwo,practicalequipunittwo)) as practicalequiptwo";
            pagination.p_tablename   = "HRS_FIREEQUIP t";
            pagination.conditionJson = string.Format(" 1=1 ");
            pagination.sidx          = "createdate"; //排序字段
            pagination.sord          = "desc";       //排序方式
            Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();

            if (!user.IsSystem)
            {
                pagination.conditionJson += string.Format(" and CREATEUSERORGCODE ='{0}'", user.OrganizeCode);
            }

            var watch       = CommonHelper.TimerStart();
            var exportTable = FireEquipbll.GetPageList(pagination, queryJson);

            //设置导出格式
            //ExcelConfig excelconfig = new ExcelConfig();
            //excelconfig.Title = "基本防护装备配备标准";
            //excelconfig.TitleFont = "微软雅黑";
            //excelconfig.TitlePoint = 16;
            //excelconfig.FileName = "基本防护装备配备标准.xls";
            //excelconfig.IsAllSizeColumn = true;
            ////每一列的设置,没有设置的列信息,系统将按datatable中的列名导出
            //List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
            //excelconfig.ColumnEntity = listColumnEntity;
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "idx", ExcelColumn = "序号", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipmentname", ExcelColumn = "名称", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "purpose", ExcelColumn = "主要用途", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipone", ExcelColumn = "一级站配备", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipratioone", ExcelColumn = "一级站备份比", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "practicalequipone", ExcelColumn = "一级站实际配备数量", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equiptwo", ExcelColumn = "二级站配备", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipratiotwo", ExcelColumn = "二级站备份比", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "practicalequiptwo", ExcelColumn = "二级站实际配备数量", Alignment = "center" });

            ////调用导出方法
            //ExcelHelper.ExcelDownload(data, excelconfig);
            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
            string fName             = "基本防护装备配备标准_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
            var num = wb.Worksheets[0].Cells.Columns.Count;

            Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
            Aspose.Cells.Cell      cell  = sheet.Cells[0, 0];
            cell.PutValue("基本防护装备配备标准"); //标题
            cell.Style.Pattern    = BackgroundType.Solid;
            cell.Style.Font.Size  = 16;
            cell.Style.Font.Color = Color.Black;
            List <string> colList = new List <string>()
            {
                "名称", "主要用途", "一级站配备", "一级站备份比", "一级站实际配备数量", "二级站配备", "二级站备份比", "二级站实际配备数量"
            };
            List <string> colList1 = new List <string>()
            {
                "equipmentname", "purpose", "equipone", "equipratioone", "practicalequipone", "equiptwo", "equipratiotwo", "practicalequiptwo"
            };

            for (int i = 0; i < colList.Count; i++)
            {
                //序号列
                Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
                serialcell.PutValue(" ");

                for (int j = 0; j < colList.Count; j++)
                {
                    Aspose.Cells.Cell curcell = sheet.Cells[1, j];
                    //sheet.Cells.SetColumnWidth(j, 40);
                    curcell.Style.Pattern    = BackgroundType.Solid;
                    curcell.Style.Font.Size  = 12;
                    curcell.Style.Font.Color = Color.Black;
                    curcell.PutValue(colList[j].ToString()); //列头
                }
                Aspose.Cells.Cells cells = sheet.Cells;
                cells.Merge(0, 0, 1, colList.Count);
            }
            for (int i = 0; i < exportTable.Rows.Count; i++)
            {
                //内容填充
                for (int j = 0; j < colList1.Count; j++)
                {
                    Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j];
                    curcell.PutValue(exportTable.Rows[i][colList1[j]].ToString());
                }
            }
            HttpResponse resp = System.Web.HttpContext.Current.Response;

            //wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
            wb.Save(Server.UrlEncode(fName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);

            return(Success("导出成功。"));
        }
Ejemplo n.º 21
0
 //Quang Huy 2014-02-19
 /// <summary>
 /// Merge cells
 /// </summary>
 /// <param name="cellNameStart">tên của ô bắt đầu merge</param>
 /// <param name="rowNumber">số dòng muốn merge</param>
 /// <param name="collumnNumber">số cột muốn merge</param>
 public void Merge(string cellNameStart, int rowNumber, int collumnNumber)
 {
     Aspose.Cells.Cell cell = _worksheet.Cells[cellNameStart];
     _worksheet.Cells.Merge(cell.Row, cell.Column, rowNumber, collumnNumber);
 }
Ejemplo n.º 22
0
        public ActionResult ExportExcel(string queryJson, int mode)
        {
            string     fileName   = string.Empty;
            Pagination pagination = new Pagination();

            pagination.page = 1;
            pagination.rows = 100000000;
            pagination.sidx = " nvl(punishdate,sysdate-10000) desc,nvl(createdate,sysdate -10000) desc,deptsort asc,sortcode asc,userid";
            pagination.sord = " desc";
            Operator curUser = ERCHTMS.Code.OperatorProvider.Provider.Current();
            string   userId  = curUser.UserId;

            try
            {
                //取出扣分数据源
                DataTable exportTable = lllegaldeductmarksbll.GetLllegalRecordInfo(pagination, queryJson);

                //取出人员积分数据源
                pagination.sidx = " deptsort asc,sortcode asc,userid";
                DataTable userTable = lllegaldeductmarksbll.GetLllegalPointInfo(pagination, queryJson);

                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
                //生产部门
                if (mode == 0)
                {
                    wb.Open(Server.MapPath("~/Resource/ExcelTemplate/生产部门违章档案(厂部)模板.xls"));
                    fileName = "生产部门违章档案(厂部)" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                }
                else
                {
                    wb.Open(Server.MapPath("~/Resource/ExcelTemplate/外协单位违章档案(厂部)模板.xls"));
                    fileName = "外协单位违章档案(厂部)" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                }
                string title  = string.Empty;
                string title0 = string.Empty;
                //第一张表
                Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
                Cells cells = sheet.Cells;
                //第二张表
                Aspose.Cells.Worksheet sheet1 = wb.Worksheets[1] as Aspose.Cells.Worksheet;
                Cells cells1 = sheet1.Cells;

                Aspose.Cells.Cell cell0  = sheet.Cells[0, 0];
                Aspose.Cells.Cell cell00 = sheet1.Cells[0, 0];
                if (mode == 0)
                {
                    title = curUser.OrganizeName + "生产部门个人违章扣分表";
                    cell0.PutValue(title);

                    title0 = curUser.OrganizeName + "生产部门个人违章档案(厂级)";
                    cell00.PutValue(title0);
                }
                else
                {
                    title = curUser.OrganizeName + "外协单位个人违章扣分表";
                    cell0.PutValue(title);

                    title0 = curUser.OrganizeName + "外协单位个人违章档案(厂级)";
                    cell00.PutValue(title0);
                }

                int rowIndex = 2;
                //遍历行
                #region 第一张表
                foreach (DataRow row in exportTable.Rows)
                {
                    if (rowIndex - 2 < exportTable.Rows.Count - 1)
                    {
                        cells.CopyRow(cells, rowIndex, rowIndex + 1);
                    }

                    string            username = !string.IsNullOrEmpty(row["username"].ToString()) ? row["username"].ToString() : "";
                    Aspose.Cells.Cell rcell1   = sheet.Cells[rowIndex, 1];
                    rcell1.PutValue(username);

                    string            deptname = !string.IsNullOrEmpty(row["deptname"].ToString()) ? row["deptname"].ToString() : "";
                    Aspose.Cells.Cell rcell2   = sheet.Cells[rowIndex, 2];
                    rcell2.PutValue(deptname);

                    string            teamname = !string.IsNullOrEmpty(row["teamname"].ToString()) ? row["teamname"].ToString() : "";
                    Aspose.Cells.Cell rcell3   = sheet.Cells[rowIndex, 3];
                    rcell3.PutValue(teamname);

                    string            dutyname = !string.IsNullOrEmpty(row["dutyname"].ToString()) ? row["dutyname"].ToString() : "";
                    Aspose.Cells.Cell rcell4   = sheet.Cells[rowIndex, 4];
                    rcell4.PutValue(dutyname);

                    string            lllegaldescribe = !string.IsNullOrEmpty(row["lllegaldescribe"].ToString()) ? row["lllegaldescribe"].ToString() : "";
                    Aspose.Cells.Cell rcell5          = sheet.Cells[rowIndex, 5];
                    rcell5.PutValue(lllegaldescribe);

                    string            lllegaltypename = !string.IsNullOrEmpty(row["lllegaltypename"].ToString()) ? row["lllegaltypename"].ToString() : "";
                    Aspose.Cells.Cell rcell6          = sheet.Cells[rowIndex, 6];
                    rcell6.PutValue(lllegaltypename);

                    string            punishdate = !string.IsNullOrEmpty(row["punishdate"].ToString()) ? Convert.ToDateTime(row["punishdate"].ToString()).ToString("yyyy-MM-dd") : "";
                    Aspose.Cells.Cell rcell7     = sheet.Cells[rowIndex, 7];
                    rcell7.PutValue(punishdate);

                    string            punishresult = !string.IsNullOrEmpty(row["punishresult"].ToString()) ? row["punishresult"].ToString() : "0";
                    Aspose.Cells.Cell rcell8       = sheet.Cells[rowIndex, 8];
                    rcell8.PutValue(row["punishresult"].ToString());

                    string            punishpoint = !string.IsNullOrEmpty(row["punishpoint"].ToString()) ? row["punishpoint"].ToString() : "0";
                    Aspose.Cells.Cell rcell9      = sheet.Cells[rowIndex, 9];
                    rcell9.PutValue(punishpoint);
                    rcell9.R1C1Formula = "=I" + (rowIndex + 1).ToString() + "/100";

                    rowIndex += 1;
                }
                #endregion

                rowIndex = 2;
                #region 第二张表
                foreach (DataRow row in userTable.Rows)
                {
                    if (rowIndex - 2 < userTable.Rows.Count - 1)
                    {
                        cells1.CopyRow(cells1, rowIndex, rowIndex + 1);
                    }
                    string            username = !string.IsNullOrEmpty(row["username"].ToString()) ? row["username"].ToString() : "";
                    Aspose.Cells.Cell rcell1   = sheet1.Cells[rowIndex, 1];
                    rcell1.PutValue(username);

                    string            deptname = !string.IsNullOrEmpty(row["deptname"].ToString()) ? row["deptname"].ToString() : "";
                    Aspose.Cells.Cell rcell2   = sheet1.Cells[rowIndex, 2];
                    rcell2.PutValue(deptname);

                    string            teamname = !string.IsNullOrEmpty(row["teamname"].ToString()) ? row["teamname"].ToString() : "";
                    Aspose.Cells.Cell rcell3   = sheet1.Cells[rowIndex, 3];
                    rcell3.PutValue(teamname);

                    string            dutyname = !string.IsNullOrEmpty(row["dutyname"].ToString()) ? row["dutyname"].ToString() : "";
                    Aspose.Cells.Cell rcell4   = sheet1.Cells[rowIndex, 4];
                    rcell4.PutValue(dutyname);

                    string            initpoint = !string.IsNullOrEmpty(row["initpoint"].ToString()) ? row["initpoint"].ToString() : "100";
                    Aspose.Cells.Cell rcell5    = sheet1.Cells[rowIndex, 5];
                    rcell5.PutValue(initpoint);

                    Aspose.Cells.Cell rcell6 = sheet1.Cells[rowIndex, 6];
                    if (mode == 0)
                    {
                        rcell6.R1C1Formula = "=SUMIF(生产部门违章扣分表!$B$3:$J$10000,B" + (rowIndex + 1).ToString() + ",生产部门违章扣分表!$J$3:$J$10000)";
                    }
                    else
                    {
                        rcell6.R1C1Formula = "=SUMIF(外协单位违章扣分表!$B$3:$J$10000,B" + (rowIndex + 1).ToString() + ",外协单位违章扣分表!$J$3:$J$10000)";
                    }

                    Aspose.Cells.Cell rcell7 = sheet1.Cells[rowIndex, 7];
                    rcell7.R1C1Formula = "=F" + (rowIndex + 1).ToString() + "-G" + (rowIndex + 1).ToString();

                    rowIndex += 1;
                }
                #endregion

                string tempSavePath = Server.MapPath("~/Resource/Temp/") + fileName;
                wb.Save(tempSavePath);
                string url = "../../Utility/DownloadFile?filePath=~/Resource/Temp/" + fileName + "&speed=10240000&newFileName=" + fileName;
                return(Redirect(url));
            }
            catch (Exception ex)
            {
                return(Error(ex.Message));
            }
        }
        private Workbook AggregateDocuments(SortedDictionary <string, List <Document> > DMDocs)
        {
            const int VALUE_COLUMN_WIDTH = 12;

            Aspose.Cells.Workbook AggregateBook       = null;
            const string          AggregateFilePrefix = "Aggregate_";
            string        ErrorMessage     = string.Empty;
            List <byte[]> FilesToAggregate = new List <byte[]>();

            byte[] AggregateFile; bool AggregateTemplateFound = false;

            #region Get the document containing the aggregate filename

            foreach (string dm in DMDocs.Keys)
            {
                foreach (IDnsPersistentDocument doc in DMDocs[dm])
                {
                    Stream contentStream = doc.ReadStream();
                    //doc is a zipped file.
                    Dictionary <string, byte[]> ExtractedFiles = ExtractZipFile(contentStream, doc.Name);
                    if (ExtractedFiles.Count() <= 0)
                    {
                        ExtractedFiles.Add(doc.Name, GetBytesFromStream(contentStream, doc.BodySize));
                    }

                    foreach (string key in ExtractedFiles.Keys)
                    {
                        if (key.StartsWith(AggregateFilePrefix))
                        {
                            if (!AggregateTemplateFound)
                            {
                                AggregateFile          = ExtractedFiles[key];
                                AggregateTemplateFound = true;
                                //Load the aggregate file.
                                MemoryStream ms = new MemoryStream();
                                ms.Write(AggregateFile, 0, AggregateFile.Length);
                                ms.Position = 0;
                                try
                                {
                                    AggregateBook = new Aspose.Cells.Workbook(ms);
                                }
                                catch (Exception) { AggregateBook = null; }
                                ms.Close(); AggregateFile = null;

                                if (AggregateBook != null)
                                {
                                    //SAS exported Aggregate template contains formula fields but suppressed by apostrophs(') in the beginning.
                                    //Remove apostrophs from the aggregate template so that suppressed formulae are restored.
                                    foreach (Aspose.Cells.Worksheet ws in AggregateBook.Worksheets)
                                    {
                                        foreach (Aspose.Cells.Cell cl in ws.Cells)
                                        {
                                            if (!cl.IsFormula &&
                                                !string.IsNullOrEmpty(cl.StringValue) &&
                                                cl.StringValue.StartsWith("="))
                                            {
                                                cl.Formula = cl.StringValue;
                                            }
                                        }
                                    }

                                    AggregateTemplateFound = true;
                                }
                            }
                        }
                        else
                        {
                            FilesToAggregate.Add(ExtractedFiles[key]);
                        }
                    }
                }
            }

            #endregion

            if (AggregateTemplateFound)
            {
                //Iterate through each datamart. Aggregate the files from each datamart into the aggregate book.
                foreach (byte[] bytes in FilesToAggregate)
                {
                    //SKIP the Aggregate Template File which are uploaded by each DataMart.
                    //Create a Source Workbook from Byte Arrary obtained from Database.
                    MemoryStream ms = new MemoryStream();
                    ms.Write(bytes, 0, bytes.Length);
                    ms.Position = 0;

                    //If Memorystream cannot be read as Aspose Cells, then ignore and move on to next memory stream.
                    Aspose.Cells.Workbook FileToAggregate = null;
                    try
                    {
                        FileToAggregate = new Aspose.Cells.Workbook(ms);
                    }
                    catch (Exception) { FileToAggregate = null; }
                    ms.Close();

                    if (FileToAggregate != null)
                    {
                        //Copy the worksheets from Source Book (ws) to the corresponding worksheet in the Aggregate Book.
                        foreach (Aspose.Cells.Worksheet ws in FileToAggregate.Worksheets)
                        {
                            Aspose.Cells.Worksheet targetsheet = AggregateBook.Worksheets[ws.Name];
                            if (targetsheet != null)
                            {
                                foreach (Aspose.Cells.Cell wcell in ws.Cells)
                                {
                                    Aspose.Cells.Cell tcell = targetsheet.Cells[wcell.Row, wcell.Column];
                                    if (tcell != null)
                                    {
                                        tcell.Copy(wcell);
                                    }
                                }
                            }
                        }
                    }
                }
                /* Set the formula field width = 12 characters */
                foreach (Aspose.Cells.Worksheet ws in AggregateBook.Worksheets)
                {
                    foreach (Aspose.Cells.Column col in ws.Cells.Columns)
                    {
                        foreach (Aspose.Cells.Cell fld in ws.Cells)
                        {
                            if (col.Index == fld.Column && (fld.IsFormula || fld.Type == Aspose.Cells.CellValueType.IsNumeric))
                            {
                                col.Width = VALUE_COLUMN_WIDTH;
                                break;
                            }
                        }
                    }
                }
            }
            else
            {
                ErrorMessage = "Cannot locate the Aggregate Template File.";
                throw (new Exception(ErrorMessage));
            }

            return(AggregateBook);
        }
Ejemplo n.º 24
0
        public ActionResult ExportExamineData(string queryJson)
        {
            try
            {
                Operator user = OperatorProvider.Provider.Current();

                Pagination pagination = new Pagination();
                pagination.page = 1;
                pagination.rows = 1000000000;

                var watch = CommonHelper.TimerStart();
                pagination.p_kid = "t.id";
                var table    = @"(select 
                                        examinetodeptid,wm_concat(id) id,
                                        examinetodept,max(createdate) createdate,
                                          wm_concat(distinct(examineperson)) examineperson,
                                           to_char(min(examinetime),'yyyy-MM-dd')||'~'|| to_char(max(examinetime),'yyyy-MM-dd') examinetime,
                                        sum(examinemoney) examinemoney,
                                        wm_concat(examinetype) examinetype,createuserorgcode
                                        from epg_dailyexamine t where 1=1 {0} group by examinetodeptid,examinetodept,createuserorgcode) t";
                var strWhere = string.Empty;
                pagination.p_fields = @" t.examinetodeptid,
                                       t.examinetodept,
                                       t.examineperson,
                                       t.examinetime,
                                       t.examinemoney,
                                       t.examinetype";

                pagination.conditionJson = "1=1";
                pagination.sidx          = "t.createdate";
                pagination.sord          = "desc";
                if (!user.IsSystem)
                {
                    pagination.conditionJson += " and t.createuserorgcode='" + user.OrganizeCode + "'";
                }
                if (!string.IsNullOrEmpty(queryJson))
                {
                    var queryParam = queryJson.ToJObject();
                    if (!queryParam["examinetodeptid"].IsEmpty())
                    {
                        strWhere += " and t.examinetodeptid ='" + queryParam["examinetodeptid"].ToString() + "'";
                    }
                    if (!queryParam["examinetype"].IsEmpty())
                    {
                        strWhere += " and t.examinetype='" + queryParam["examinetype"].ToString() + "'";
                    }
                    if (!queryParam["examinecontent"].IsEmpty())
                    {
                        strWhere += " and t.examinecontent like '%" + queryParam["examinecontent"].ToString() + "%'";
                    }
                    //开始时间
                    if (!queryParam["sTime"].IsEmpty())
                    {
                        strWhere += string.Format(@" and t.examinetime >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", queryParam["sTime"].ToString());
                    }
                    //结束时间
                    if (!queryParam["eTime"].IsEmpty())
                    {
                        strWhere += string.Format(@" and t.examinetime < to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Convert.ToDateTime(queryParam["eTime"].ToString()).AddDays(1).ToString("yyyy-MM-dd"));
                    }
                }
                table = string.Format(table, strWhere);
                pagination.p_tablename = table;
                var data = dailyexaminebll.GetExportExamineCollent(pagination, queryJson);
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
                string fName             = "日常考核汇总_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
                var num = wb.Worksheets[0].Cells.Columns.Count;

                Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
                Aspose.Cells.Cell      cell  = sheet.Cells[0, 0];
                cell.PutValue("考核汇总表"); //标题
                cell.Style.Pattern    = BackgroundType.Solid;
                cell.Style.Font.Size  = 16;
                cell.Style.Font.Color = Color.Black;
                List <string> colList = new List <string>()
                {
                    "被考核单位", "考核金额", "考核类型", "考核人", "考核时间"
                };
                List <string> colList1 = new List <string>()
                {
                    "examinetodept", "examinemoney", "examinetype", "examineperson", "examinetime"
                };
                for (int i = 0; i < colList.Count; i++)
                {
                    //序号列
                    Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
                    serialcell.PutValue(" ");

                    for (int j = 0; j < colList.Count; j++)
                    {
                        Aspose.Cells.Cell curcell = sheet.Cells[1, j + 1];
                        sheet.Cells.SetColumnWidth(j + 1, 40);
                        curcell.Style.Pattern    = BackgroundType.Solid;
                        curcell.Style.Font.Size  = 12;
                        curcell.Style.Font.Color = Color.Black;
                        curcell.PutValue(colList[j].ToString()); //列头
                    }
                    Aspose.Cells.Cells cells = sheet.Cells;
                    cells.Merge(0, 0, 1, colList.Count + 1);
                }
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    //序列号
                    Aspose.Cells.Cell serialcell = sheet.Cells[i + 2, 0];
                    if (string.IsNullOrWhiteSpace(data.Rows[i]["parent"].ToString()))
                    {
                        serialcell.PutValue("合计");
                    }

                    //内容填充
                    for (int j = 0; j < colList1.Count; j++)
                    {
                        Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j + 1];
                        curcell.PutValue(data.Rows[i][colList1[j]].ToString());
                    }
                }
                HttpResponse resp = System.Web.HttpContext.Current.Response;
                wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
                return(Success("导出成功。", fName));
            }
            catch (Exception)
            {
                throw;
            }
        }
Ejemplo n.º 25
0
        public static void XuatDuLieuRaExcel(int iRowPara,
                                             int iColumnPara,
                                             string strSubHeaderPara,
                                             System.Data.DataTable tblBangDuLieuPara,
                                             string strTemplateNamePara)
        {
            //Đường dẫn file template
            string strSourceFilePri = string.Format("{0}{1}{2}",
                                                    System.Windows.Forms.Application.StartupPath,
                                                    PATH_TEMPLATES, strTemplateNamePara);

            SaveFileDialog saveFileDialogPri = new SaveFileDialog();

            saveFileDialogPri.Filter      = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
            saveFileDialogPri.FilterIndex = 1;

            if (saveFileDialogPri.ShowDialog() == DialogResult.OK)
            {
                FileStream streamTemp = new FileStream(strSourceFilePri, FileMode.Open);

                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                workbook.Open(streamTemp);
                workbook.Worksheets.Add();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                //Set cell store subHeader
                Aspose.Cells.Cells cellHeader = worksheet.Cells;
                //cellHeader.Merge(2, 0, 1, tblBangDuLieuPara.Columns.Count);
                worksheet.Cells["A3"].PutValue(strSubHeaderPara);
                //worksheet.IsGridlinesVisible = false;
                worksheet.Cells.ImportDataTable(tblBangDuLieuPara, false, iRowPara, iColumnPara, tblBangDuLieuPara.Rows.Count, tblBangDuLieuPara.Columns.Count);

                //Formatting for cells store database
                for (int i = 0; i < tblBangDuLieuPara.Rows.Count; i++)
                {
                    for (int j = 0; j < tblBangDuLieuPara.Columns.Count; j++)
                    {
                        Aspose.Cells.Cell cell = worksheet.Cells[iRowPara + i, j];
                        workbook.Styles.Add();
                        Aspose.Cells.Style style = cell.GetStyle();
                        style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                        style.Borders[BorderType.BottomBorder].Color     = Color.Silver;
                        style.Borders[BorderType.TopBorder].LineStyle    = CellBorderType.Thin;
                        style.Borders[BorderType.TopBorder].Color        = Color.Silver;
                        style.Borders[BorderType.LeftBorder].LineStyle   = CellBorderType.Thin;
                        style.Borders[BorderType.LeftBorder].Color       = Color.Silver;
                        style.Borders[BorderType.RightBorder].LineStyle  = CellBorderType.Thin;
                        style.Borders[BorderType.RightBorder].Color      = Color.Silver;
                        cell.SetStyle(style);
                    }
                }
                //worksheet.AutoFitColumns();
                //Save excel file
                workbook.Save(saveFileDialogPri.FileName, FileFormatType.Default);

                MessageBox.Show(WorkingContext.LangManager.GetString("frmRestSheet_ExportExcel_Messa"),
                                WorkingContext.LangManager.GetString("Message"),
                                MessageBoxButtons.OK, MessageBoxIcon.Information);

                streamTemp.Close();

                if (File.Exists(saveFileDialogPri.FileName))
                {
                    Process.Start(saveFileDialogPri.FileName);
                }
            }
        }
Ejemplo n.º 26
0
        public static void Main(string[] args)
        {
            // The path to the documents directory.
            string dataDir = Path.GetFullPath("../../../Data/");

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            //Create a workbook.
            Workbook workbook = new Workbook();

            //Obtaining the reference of the default(first) worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Obtaining Worksheet's cells collection
            Cells cells = sheet.Cells;

            //Setting the value to the cells
            Aspose.Cells.Cell cell = cells["A1"];
            cell.PutValue("Employee");
            cell = cells["B1"];
            cell.PutValue("Quarter");
            cell = cells["C1"];
            cell.PutValue("Product");
            cell = cells["D1"];
            cell.PutValue("Continent");
            cell = cells["E1"];
            cell.PutValue("Country");
            cell = cells["F1"];
            cell.PutValue("Sale");

            cell = cells["A2"];
            cell.PutValue("David");
            cell = cells["A3"];
            cell.PutValue("David");
            cell = cells["A4"];
            cell.PutValue("David");
            cell = cells["A5"];
            cell.PutValue("David");
            cell = cells["A6"];
            cell.PutValue("James");
            cell = cells["A7"];
            cell.PutValue("James");
            cell = cells["A8"];
            cell.PutValue("James");
            cell = cells["A9"];
            cell.PutValue("James");
            cell = cells["A10"];
            cell.PutValue("James");
            cell = cells["A11"];
            cell.PutValue("Miya");
            cell = cells["A12"];
            cell.PutValue("Miya");
            cell = cells["A13"];
            cell.PutValue("Miya");
            cell = cells["A14"];
            cell.PutValue("Miya");
            cell = cells["A15"];
            cell.PutValue("Miya");


            cell = cells["B2"];
            cell.PutValue(1);
            cell = cells["B3"];
            cell.PutValue(2);
            cell = cells["B4"];
            cell.PutValue(3);
            cell = cells["B5"];
            cell.PutValue(4);
            cell = cells["B6"];
            cell.PutValue(1);
            cell = cells["B7"];
            cell.PutValue(2);
            cell = cells["B8"];
            cell.PutValue(3);
            cell = cells["B9"];
            cell.PutValue(4);
            cell = cells["B10"];
            cell.PutValue(4);
            cell = cells["B11"];
            cell.PutValue(1);
            cell = cells["B12"];
            cell.PutValue(1);
            cell = cells["B13"];
            cell.PutValue(2);
            cell = cells["B14"];
            cell.PutValue(2);
            cell = cells["B15"];
            cell.PutValue(2);

            cell = cells["C2"];
            cell.PutValue("Maxilaku");
            cell = cells["C3"];
            cell.PutValue("Maxilaku");
            cell = cells["C4"];
            cell.PutValue("Chai");
            cell = cells["C5"];
            cell.PutValue("Maxilaku");
            cell = cells["C6"];
            cell.PutValue("Chang");
            cell = cells["C7"];
            cell.PutValue("Chang");
            cell = cells["C8"];
            cell.PutValue("Chang");
            cell = cells["C9"];
            cell.PutValue("Chang");
            cell = cells["C10"];
            cell.PutValue("Chang");
            cell = cells["C11"];
            cell.PutValue("Geitost");
            cell = cells["C12"];
            cell.PutValue("Chai");
            cell = cells["C13"];
            cell.PutValue("Geitost");
            cell = cells["C14"];
            cell.PutValue("Geitost");
            cell = cells["C15"];
            cell.PutValue("Geitost");

            cell = cells["D2"];
            cell.PutValue("Asia");
            cell = cells["D3"];
            cell.PutValue("Asia");
            cell = cells["D4"];
            cell.PutValue("Asia");
            cell = cells["D5"];
            cell.PutValue("Asia");
            cell = cells["D6"];
            cell.PutValue("Europe");
            cell = cells["D7"];
            cell.PutValue("Europe");
            cell = cells["D8"];
            cell.PutValue("Europe");
            cell = cells["D9"];
            cell.PutValue("Europe");
            cell = cells["D10"];
            cell.PutValue("Europe");
            cell = cells["D11"];
            cell.PutValue("America");
            cell = cells["D12"];
            cell.PutValue("America");
            cell = cells["D13"];
            cell.PutValue("America");
            cell = cells["D14"];
            cell.PutValue("America");
            cell = cells["D15"];
            cell.PutValue("America");


            cell = cells["E2"];
            cell.PutValue("China");
            cell = cells["E3"];
            cell.PutValue("India");
            cell = cells["E4"];
            cell.PutValue("Korea");
            cell = cells["E5"];
            cell.PutValue("India");
            cell = cells["E6"];
            cell.PutValue("France");
            cell = cells["E7"];
            cell.PutValue("France");
            cell = cells["E8"];
            cell.PutValue("Germany");
            cell = cells["E9"];
            cell.PutValue("Italy");
            cell = cells["E10"];
            cell.PutValue("France");
            cell = cells["E11"];
            cell.PutValue("U.S.");
            cell = cells["E12"];
            cell.PutValue("U.S.");
            cell = cells["E13"];
            cell.PutValue("Brazil");
            cell = cells["E14"];
            cell.PutValue("U.S.");
            cell = cells["E15"];
            cell.PutValue("U.S.");


            cell = cells["F2"];
            cell.PutValue(2000);
            cell = cells["F3"];
            cell.PutValue(500);
            cell = cells["F4"];
            cell.PutValue(1200);
            cell = cells["F5"];
            cell.PutValue(1500);
            cell = cells["F6"];
            cell.PutValue(500);
            cell = cells["F7"];
            cell.PutValue(1500);
            cell = cells["F8"];
            cell.PutValue(800);
            cell = cells["F9"];
            cell.PutValue(900);
            cell = cells["F10"];
            cell.PutValue(500);
            cell = cells["F11"];
            cell.PutValue(1600);
            cell = cells["F12"];
            cell.PutValue(600);
            cell = cells["F13"];
            cell.PutValue(2000);
            cell = cells["F14"];
            cell.PutValue(500);
            cell = cells["F15"];
            cell.PutValue(900);

            //Adding a new List Object to the worksheet
            Aspose.Cells.Tables.ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add("A1", "F15", true)];

            //Adding Default Style to the table
            listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium10;

            //Show Total
            listObject.ShowTotals = true;

            //Set the Quarter field's calculation type
            listObject.ListColumns[1].TotalsCalculation = Aspose.Cells.Tables.TotalsCalculation.Count;


            //Saving the Excel file
            workbook.Save(dataDir + "List_Object.xlsx");
        }
        public ActionResult Export(string queryJson)
        {
            Pagination pagination = new Pagination();

            pagination.page        = 1;
            pagination.rows        = 100000000;
            pagination.p_kid       = "rownum idx";
            pagination.p_fields    = @"t.Name,(Concat(t.Specification,t.SpecificationUnit)) as Specification,
t.risktype,t.site,
(Concat(t.receivenum,t.receiveUnit)) as receivenum,t.receiveuser,t.grantuser,t.purpose";
            pagination.p_tablename = "XLD_DANGEROUSCHEMICALRECEIVE t  left join XLD_DANGEROUSCHEMICAL t1 on t.mainid=t1.id";

            //pagination.sidx = "createdate";//排序字段
            //pagination.sord = "desc";//排序方式
            Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();

            if (!user.IsSystem)
            {
                pagination.conditionJson = "1=1";
            }
            else
            {
                pagination.conditionJson = string.Format(" CREATEUSERORGCODE ='{0}'", user.OrganizeCode);
            }

            var watch = CommonHelper.TimerStart();
            var data  = DangerChemicalsReceiveBll.GetList(pagination, queryJson);

            HttpResponse resp = System.Web.HttpContext.Current.Response;

            // 详细列表内容
            string fielname = "危化品领用记录" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
            wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
            Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;

            Aspose.Cells.Cell cell = sheet.Cells[0, 0];
            cell.PutValue("危化品领用记录"); //标题
            cell.Style.Pattern    = BackgroundType.Solid;
            cell.Style.Font.Size  = 16;
            cell.Style.Font.Color = Color.Black;

            //Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
            //style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;//文字居中
            //style.IsTextWrapped = true;//自动换行
            //style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
            //style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
            //style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
            //style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;

            Aspose.Cells.Cells cells = sheet.Cells;
            cells.Merge(0, 0, 1, 9);

            sheet.Cells[1, 0].PutValue("序号");
            sheet.Cells[1, 1].PutValue("名称");
            sheet.Cells[1, 2].PutValue("规格");
            sheet.Cells[1, 3].PutValue("危险品类型");
            sheet.Cells[1, 4].PutValue("存放地点");
            sheet.Cells[1, 5].PutValue("领用数量");
            sheet.Cells[1, 6].PutValue("领用人");
            sheet.Cells[1, 7].PutValue("发放人");
            sheet.Cells[1, 8].PutValue("用途及使用说明");
            sheet.Cells.SetColumnWidthPixel(8, 250);

            int rowIndex = 2;

            foreach (DataRow row in data.Rows)
            {
                Aspose.Cells.Cell idxcell = sheet.Cells[rowIndex, 0];
                idxcell.PutValue(row["idx"]);
                Aspose.Cells.Cell namexcell = sheet.Cells[rowIndex, 1];
                namexcell.PutValue(row["name"]);
                Aspose.Cells.Cell specificationcell = sheet.Cells[rowIndex, 2];
                specificationcell.PutValue(row["specification"]);
                Aspose.Cells.Cell risktypexcell = sheet.Cells[rowIndex, 3];
                risktypexcell.PutValue(row["risktype"]);
                Aspose.Cells.Cell sitexcell = sheet.Cells[rowIndex, 4];
                sitexcell.PutValue(row["site"]);
                Aspose.Cells.Cell receivenumxcell = sheet.Cells[rowIndex, 5];
                receivenumxcell.PutValue(row["receivenum"]);
                Aspose.Cells.Cell receiveuserxcell = sheet.Cells[rowIndex, 6];
                receiveuserxcell.PutValue(row["receiveuser"]);
                Aspose.Cells.Cell grantuserxcell = sheet.Cells[rowIndex, 7];
                grantuserxcell.PutValue(row["grantuser"]);
                Aspose.Cells.Cell purposecell = sheet.Cells[rowIndex, 8];
                purposecell.PutValue(row["purpose"]);

                rowIndex++;
            }
            //设置导出格式
            //ExcelConfig excelconfig = new ExcelConfig();
            //excelconfig.Title = "危化品领用记录";
            //excelconfig.TitleFont = "微软雅黑";
            //excelconfig.TitlePoint = 16;
            //excelconfig.FileName = "危化品领用记录.xls";
            //excelconfig.IsAllSizeColumn = true;
            ////每一列的设置,没有设置的列信息,系统将按datatable中的列名导出
            //List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
            //excelconfig.ColumnEntity = listColumnEntity;
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "idx", ExcelColumn = "序号", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "name", ExcelColumn = "名称", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "specification", ExcelColumn = "规格", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "risktype", ExcelColumn = "危险品类型", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "site", ExcelColumn = "存放地点", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "receivenum", ExcelColumn = "领用数量", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "receiveuser", ExcelColumn = "领用人", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "grantuser", ExcelColumn = "发放人", Alignment = "center" });
            //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "purpose", ExcelColumn = "用途及使用说明", Alignment = "center" });
            ////调用导出方法
            //ExcelHelper.ExcelDownload(data, excelconfig);
            wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);

            return(Success("导出成功。"));
        }
Ejemplo n.º 28
0
        public ActionResult Export(string queryJson)
        {
            try
            {
                var exportTable = SafetyMeshbll.GetTableList(queryJson);

                ////调用导出方法
                //ExcelHelper.ExcelDownload(data, excelconfig);
                Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
                string fName             = "安全网格_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
                var num = wb.Worksheets[0].Cells.Columns.Count;

                Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
                Aspose.Cells.Cell      cell  = sheet.Cells[0, 0];
                cell.PutValue("安全网格"); //标题
                cell.Style.Pattern    = BackgroundType.Solid;
                cell.Style.Font.Size  = 16;
                cell.Style.Font.Color = Color.Black;
                List <string> colList = new List <string>()
                {
                    "网格名称", "上级网格名称", "网络责任人", "联系电话", "关联区域", "网络级别", "工作职责"
                };
                List <string> colList1 = new List <string>()
                {
                    "meshname", "superiorname", "dutyuser", "dutytel", "district", "meshrank", "workjob"
                };
                for (int i = 0; i < colList.Count; i++)
                {
                    //序号列
                    Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
                    serialcell.PutValue(" ");

                    for (int j = 0; j < colList.Count; j++)
                    {
                        Aspose.Cells.Cell curcell = sheet.Cells[1, j];
                        //sheet.Cells.SetColumnWidth(j, 40);
                        curcell.Style.Pattern    = BackgroundType.Solid;
                        curcell.Style.Font.Size  = 12;
                        curcell.Style.Font.Color = Color.Black;
                        curcell.PutValue(colList[j].ToString()); //列头
                    }
                    Aspose.Cells.Cells cells = sheet.Cells;
                    cells.Merge(0, 0, 1, colList.Count);
                }
                for (int i = 0; i < exportTable.Rows.Count; i++)
                {
                    //内容填充
                    for (int j = 0; j < colList1.Count; j++)
                    {
                        Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j];
                        curcell.PutValue(exportTable.Rows[i][colList1[j]].ToString());
                    }
                }
                HttpResponse resp = System.Web.HttpContext.Current.Response;
                //wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
                wb.Save(Server.UrlEncode(fName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);

                return(Success("导出成功。"));
            }
            catch (Exception ex)
            {
                return(Error(ex.Message));
            }
        }
Ejemplo n.º 29
0
 //Quang Huy 2014-02-19
 /// <summary>
 /// Thực hiện đặt viền cho ô
 /// </summary>
 /// <param name="cellName">Tên của ô (VD "B6")</param>
 /// <param name="position">Vị trí viền (mặc định viền xung quanh)</param>
 /// <param name="color">màu viền (mặc định đen)</param>
 public bool SetCellBorder(string cellName, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
 {
     Aspose.Cells.Cell cell = _worksheet.Cells[cellName];
     return(SetCellBorder(cell, position, color));
 }
Ejemplo n.º 30
0
        public static void Run()
        {
            //Output directory
            string outputDir = RunExamples.Get_OutputDirectory();

            // Instantiate a new workbook
            Workbook workbook = new Workbook();

            // Get the cells in the first(default) worksheet
            Cells cells = workbook.Worksheets[0].Cells;

            // Get the A1 cell
            Aspose.Cells.Cell p = cells["A1"];

            // Enter a value
            p.PutValue("Preface");

            // Get the A10 cell
            Aspose.Cells.Cell A = cells["A10"];

            // Enter a value.
            A.PutValue("page1");

            // Get the H15 cell
            Aspose.Cells.Cell D = cells["H15"];

            // Enter a value
            D.PutValue("page1(H15)");

            // Add a new worksheet to the workbook
            workbook.Worksheets.Add();

            // Get the cells in the second sheet
            cells = workbook.Worksheets[1].Cells;

            // Get the B10 cell in the second sheet
            Aspose.Cells.Cell B = cells["B10"];

            // Enter a value
            B.PutValue("page2");

            // Add a new worksheet to the workbook
            workbook.Worksheets.Add();

            // Get the cells in the third sheet
            cells = workbook.Worksheets[2].Cells;

            // Get the C10 cell in the third sheet
            Aspose.Cells.Cell C = cells["C10"];

            // Enter a value
            C.PutValue("page3");

            // Create a main PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry pbeRoot = new Aspose.Cells.Rendering.PdfBookmarkEntry();

            // Specify its text
            pbeRoot.Text = "Sections";

            // Set the destination cell/location
            pbeRoot.Destination = p;

            // Set its sub entry array list
            pbeRoot.SubEntry = new ArrayList();

            // Create a sub PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry subPbe1 = new Aspose.Cells.Rendering.PdfBookmarkEntry();

            // Specify its text
            subPbe1.Text = "Section 1";

            // Set its destination cell
            subPbe1.Destination = A;

            // Define/Create a sub Bookmark entry object of "Section A"
            Aspose.Cells.Rendering.PdfBookmarkEntry ssubPbe = new Aspose.Cells.Rendering.PdfBookmarkEntry();

            // Specify its text
            ssubPbe.Text = "Section 1.1";

            // Set its destination
            ssubPbe.Destination = D;

            // Create/Set its sub entry array list object
            subPbe1.SubEntry = new ArrayList();

            // Add the object to "Section 1"
            subPbe1.SubEntry.Add(ssubPbe);

            // Add the object to the main PDF root object
            pbeRoot.SubEntry.Add(subPbe1);

            // Create a sub PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry subPbe2 = new Aspose.Cells.Rendering.PdfBookmarkEntry();

            // Specify its text
            subPbe2.Text = "Section 2";

            // Set its destination
            subPbe2.Destination = B;

            // Add the object to the main PDF root object
            pbeRoot.SubEntry.Add(subPbe2);

            // Create a sub PDF Bookmark entry object
            Aspose.Cells.Rendering.PdfBookmarkEntry subPbe3 = new Aspose.Cells.Rendering.PdfBookmarkEntry();

            // Specify its text
            subPbe3.Text = "Section 3";

            // Set its destination
            subPbe3.Destination = C;

            // Add the object to the main PDF root object
            pbeRoot.SubEntry.Add(subPbe3);

            // Create an instance of PdfSaveOptions
            Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();

            // Set the PDF Bookmark root object
            pdfSaveOptions.Bookmark = pbeRoot;


            // Save the pdf file
            workbook.Save(outputDir + "outputAddPDFBookmarks.pdf", pdfSaveOptions);

            Console.WriteLine("AddPDFBookmarks executed successfully.\r\n");
        }