0

This question already has an answer here:

How to import excel in SharePoint List programatically in SharePoint 2013. Excel having more than 40000 entry.

marked as duplicate by Karthik Jaganathan, Robert Lindgren Mar 30 '16 at 10:47

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    As per my knowledge, This is not a duplicate question, The question you suggested which is about create a list using Excel Sheet but the question was as before Import data from list to Excel Sheet. – Jayakumar Kulkarni Mar 30 '16 at 11:19
  • why not use the import spreadsheet to sharepoint list feature? support.office.com/en-us/article/… – Mike Mar 30 '16 at 14:13
2

Fist you convert List Data into DataTable by SPListItemCollection, Then do Something like this

 using DocumentFormat.OpenXml;
     using DocumentFormat.OpenXml.Packaging; 
     using DocumentFormat.OpenXml.Spreadsheet;
     using System;
     using System.ComponentModel;
     using System.Data;
     using System.Web.UI.WebControls.WebParts;


    DataTable dtStateMaster = BindThatTableHere()//  Get DataTable fromlist or Database

    using (SpreadsheetDocument document = SpreadsheetDocument.Create(@"D:\TestWorkSheet.xlsx", SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet" }; sheets.Append(sheet); WriteDataTableToExcelWorksheet(dtStateMaster, worksheetPart); //  If you commented this line of code it will create a simple blank Excelsheet workbookPart.Workbook.Save();
                        } private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
                {
                    var worksheet = worksheetPart.Worksheet;
                    var sheetData = worksheet.GetFirstChild<SheetData>();

                    string cellValue = "";

                    //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
                    //
                    //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
                    //  cells of data, we'll know if to write Text values or Numeric cell values.
                    int numberOfColumns = dt.Columns.Count;
                    bool[] IsNumericColumn = new bool[numberOfColumns];

                    string[] excelColumnNames = new string[numberOfColumns];
                    for (int n = 0; n < numberOfColumns; n++)
                        excelColumnNames[n] = GetExcelColumnName(n);

                    //
                    //  Create the Header row in our Excel Worksheet
                    //
                    uint rowIndex = 1;

                    var headerRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet
                    sheetData.Append(headerRow);

                    for (int colInx = 0; colInx < numberOfColumns; colInx++)
                    {
                        DataColumn col = dt.Columns[colInx];
                        AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
                        IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
                    }

                    //
                    //  Now, step through each row of data in our DataTable...
                    //
                    double cellNumericValue = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        // ...create a new row, and append a set of this row's data to it.
                        ++rowIndex;
                        var newExcelRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet
                        sheetData.Append(newExcelRow);

                        for (int colInx = 0; colInx < numberOfColumns; colInx++)
                        {
                            cellValue = dr.ItemArray[colInx].ToString();

                            // Create cell with data
                            if (IsNumericColumn[colInx])
                            {
                                //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                                //  If this numeric value is NULL, then don't write anything to the Excel file.
                                cellNumericValue = 0;
                                if (double.TryParse(cellValue, out cellNumericValue))
                                {
                                    cellValue = cellNumericValue.ToString();
                                    AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                                }
                            }
                            else
                            {
                                //  For text cells, just write the input data straight out to the Excel file.
                                AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                            }
                        }
                    }
                }

                private static string GetExcelColumnName(int columnIndex)
                {
                    //  Convert a zero-based column index into an Excel column reference  (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
                    //
                    //  eg  GetExcelColumnName(0) should return "A"
                    //      GetExcelColumnName(1) should return "B"
                    //      GetExcelColumnName(25) should return "Z"
                    //      GetExcelColumnName(26) should return "AA"
                    //      GetExcelColumnName(27) should return "AB"
                    //      ..etc..
                    //
                    if (columnIndex < 26)
                        return ((char)('A' + columnIndex)).ToString();

                    char firstChar = (char)('A' + (columnIndex / 26) - 1);
                    char secondChar = (char)('A' + (columnIndex % 26));

                    return string.Format("{0}{1}", firstChar, secondChar);
                }

                private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
                {
                    //  Add a new Excel Cell to our Row 
                    Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
                    CellValue cellValue = new CellValue();
                    cellValue.Text = cellStringValue;
                    cell.Append(cellValue);
                    excelRow.Append(cell);
                }

                private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
                {
                    //  Add a new Excel Cell to our Row 
                    Cell cell = new Cell() { CellReference = cellReference };
                    CellValue cellValue = new CellValue();
                    cellValue.Text = cellStringValue;
                    cell.Append(cellValue);
                    excelRow.Append(cell);
                }
  • Add DocumentFormat.OpenXml dll into reference – Jayakumar Kulkarni Mar 30 '16 at 10:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.