Example #1
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            int intValue = 0;

            if (strValue.Contains("$"))
            {
                strValue = strValue.Replace("$", "");
                strValue = strValue.Replace(",", "");

                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (int.TryParse(strValue, out intValue))
            {
                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (string.IsNullOrEmpty(strValue))
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
            else
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
        }
Example #2
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

            int      intValue  = 0;
            DateTime dateValue = new DateTime();

            if (strValue.Contains("$"))
            {
                strValue = strValue.Replace("$", "");
                strValue = strValue.Replace(",", "");

                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (int.TryParse(strValue, out intValue))
            {
                workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
            }
            else if (string.IsNullOrEmpty(strValue))
            {
                workSheetWriter.PasteText(cellLocation, strValue);
            }
            //else if (DateTime.TryParse(strValue,out dateValue))
            //{
            //    workSheetWriter.PasteDate(cellLocation, dateValue);
            //}
            else
            {
                workSheetWriter.PasteText(cellLocation, strValue, SpreadsheetStyle.GetDefault(spreadSheet));
            }
        }
Example #3
0
        public void WorksheetMultipleStyleTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);

            style.IsItalic = true;
            style.SetBackgroundColor("FF0000");//(red)

            WorksheetWriter.PasteText(doc, worksheetPart, "B", 2, "Hello world");
            WorksheetWriter.SetStyle(style, doc, worksheetPart, "B", 2);

            style        = SpreadsheetReader.GetDefaultStyle(doc);
            style.IsBold = true;
            style.SetBackgroundColor("0000FF");//(blue)

            WorksheetWriter.SetStyle(style, doc, worksheetPart, "C", 3);
            WorksheetWriter.PasteText(doc, worksheetPart, "C", 3, "Hello world2");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled2.xlsx", GetOutputFolder()), stream);
        }
Example #4
0
        public void WorksheetAddStyleTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            SpreadsheetStyle defaultStyle = SpreadsheetReader.GetDefaultStyle(doc);

            defaultStyle.IsItalic    = true;
            defaultStyle.IsBold      = true;
            defaultStyle.IsUnderline = true;
            defaultStyle.SetColor("FF0000");
            //r = 255, g = 0, b = 0 (Red)
            defaultStyle.SetBackgroundColor("DDDDDD");
            //(light grey)
            defaultStyle.SetBorder("00FF00", BorderStyleValues.Medium);
            //(green medium border)

            WorksheetWriter.PasteText(doc, worksheetPart, "E", 5, "Hello world");
            WorksheetWriter.SetStyle(defaultStyle, doc, worksheetPart, "E", 5);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled.xlsx", GetOutputFolder()), stream);
        }
Example #5
0
        public void WorksheetDeleteRowsTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteText("D3", "Row 3");
            writer.PasteText("D4", "Row 4");
            writer.PasteText("D5", "Row 5");

            //Delete row 3
            writer.DeleteRows(3, 2);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\deleterows.xlsx", GetOutputFolder()), stream);
        }
Example #6
0
        public void WorksheetAddAlignmentTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            SpreadsheetStyle    style         = SpreadsheetReader.GetDefaultStyle(doc);
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            style.IsWrapped = true;
            writer.PasteText("E5", "Wrapped text", style);

            style.IsWrapped = false;
            style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
            writer.PasteText("E7", "Aligned Test", style);

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\wrapped.xlsx", GetOutputFolder()), stream);
        }
        /// <summary>
        /// 将查询出的DataSet导出为Excel。
        /// </summary>
        /// <param name="dataSet">要导出的DataSet数据集,可以包含多个DataTable数据,使用DataSetName作为导出的文件名</param>
        public static void ExportDataSet(DataSet dataSet)
        {
            if (dataSet == null)
            {
                throw new ArgumentException("dataSet");
            }
            if (dataSet.Tables.Count == 0)
            {
                return;
            }

            MemoryStream        stream = SpreadsheetReader.Create();
            SpreadsheetDocument doc    = SpreadsheetDocument.Open(stream, true);
            //首先清空原有的Sheet
            var allSheet = doc.WorkbookPart.Workbook.Descendants <Sheet>();

            if (allSheet.Count() > 0)
            {
                doc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
            }
            //构造Excel
            foreach (DataTable dt in dataSet.Tables)
            {
                WorksheetPart   sheetPart   = SpreadsheetWriter.InsertWorksheet(doc, dt.TableName);
                WorksheetWriter sheetWriter = new WorksheetWriter(doc, sheetPart);
                //打印列名
                int colIndex = 0;//当前第几列,从0开始
                foreach (DataColumn col in dt.Columns)
                {
                    string reference = GetExcelColumnHeader(colIndex) + "1";
                    sheetWriter.PasteText(reference, col.ColumnName);
                    colIndex++;
                }
                sheetWriter.PasteDataTable(dt, "A2");
                SpreadsheetWriter.Save(doc);
            }
            //Write to response stream
            System.Web.HttpContext context = System.Web.HttpContext.Current;
            context.Response.Clear();
            string UserAgent = context.Request.ServerVariables["http_user_agent"].ToLower();
            string FileName  = dataSet.DataSetName;

            if (UserAgent.IndexOf("firefox") == -1)
            {//非火狐浏览器
                FileName = context.Server.UrlEncode(FileName);
            }
            context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", FileName + ".xlsx"));
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            stream.WriteTo(context.Response.OutputStream);
            context.Response.End();
        }
Example #8
0
        public void InsertSheetTest()
        {
            MemoryStream        stream = SpreadsheetReader.Create();
            SpreadsheetDocument doc    = SpreadsheetDocument.Open(stream, true);

            var worksheetPart = SpreadsheetWriter.InsertWorksheet(doc);

            Assert.IsNotNull(worksheetPart, "A worksheet part was not returned.");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteText("B2", "Hello World");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\createsheet.xlsx", GetOutputFolder()), stream);
        }
Example #9
0
        public void WorksheetPrintAreaTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
            WorksheetWriter     writer        = new WorksheetWriter(doc, worksheetPart);

            writer.PasteText("A1", "Set print area to A1:B9");

            //Test setting the print area
            DefinedName area = writer.SetPrintArea("Sheet1", "A1", "B9");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\printarea.xlsx", GetOutputFolder()), stream);

            //Assert.IsTrue(area != null, "Print area reference not returned.");
        }
Example #10
0
        public void WorksheetFindCellTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            //Test that the findcell function can accurately add cells past z
            writer.PasteText("A1", "A1");
            writer.PasteText("Z1", "Z1");
            writer.PasteText("Z2", "Z2");
            writer.PasteText("AA1", "AA1");
            writer.PasteText("AA2", "AA2");
            writer.PasteText("BA1", "BA1");
            writer.PasteText("AA9", "AA9");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\findcell.xlsx", GetOutputFolder()), stream);
        }
Example #11
0
        public void WorksheetInsertTest()
        {
            MemoryStream        stream        = SpreadsheetReader.Create();
            SpreadsheetDocument doc           = SpreadsheetDocument.Open(stream, true);
            WorksheetPart       worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");

            WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

            writer.PasteNumber("D3", "2");
            writer.PasteNumber("D4", "3");
            writer.PasteNumber("D5", "4");

            //Add total without a calc chain
            writer.FindCell("D6").CellFormula = new CellFormula("SUM(D3:D5)");

            //Insert a row
            writer.InsertRow(6);
            writer.PasteText("H6", "New content test");

            //Save to the memory stream, and then to a file
            SpreadsheetWriter.Save(doc);
            SpreadsheetWriter.StreamToFile(string.Format("{0}\\insertrow.xlsx", GetOutputFolder()), stream);
        }
Example #12
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

             int intValue = 0;
             DateTime dateValue = new DateTime();
             if (strValue.Contains("$"))
             {
            strValue = strValue.Replace("$", "");
            strValue = strValue.Replace(",", "");

            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (int.TryParse(strValue, out intValue))
             {
            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (string.IsNullOrEmpty(strValue))
             {
            workSheetWriter.PasteText(cellLocation, strValue);
             }
             //else if (DateTime.TryParse(strValue,out dateValue))
             //{
             //    workSheetWriter.PasteDate(cellLocation, dateValue);
             //}
             else
             {
            workSheetWriter.PasteText(cellLocation, strValue,SpreadsheetStyle.GetDefault(spreadSheet));
             }
        }
Example #13
0
        /// <summary>
        /// Write values to the spreadsheet.
        /// </summary>
        /// <param name="cellLocation">Row Column Value.</param>
        /// <param name="strValue">Value to write.</param>
        /// <param name="spreadSheet">Spreadsheet to write to. </param>
        /// <param name="workSheet">Worksheet to write to. </param>
        private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
        {
            WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);

             int intValue = 0;
             if (strValue.Contains("$"))
             {
            strValue = strValue.Replace("$", "");
            strValue = strValue.Replace(",", "");

            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (int.TryParse(strValue, out intValue))
             {
            workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
             }
             else if (string.IsNullOrEmpty(strValue))
             {
            workSheetWriter.PasteText(cellLocation, strValue);
             }
             else
             {
            workSheetWriter.PasteText(cellLocation, strValue);
             }
        }