Importing from Excel to Sharepoint lists

I didn't really expect too much from this feature, but yesterday I had to move a large Excel file to a Sharepoint list and gave it a real try. After several attempts on the "Import Spreadsheet" I forfitted and created the list definition manually and copied in the values column by column from the source sheet.

Later on in the evening I picked up Microsoft .NET Development for Microsoft Office (yes, from Microsoft Press) and started to read about Excel interop. In the book they describe how OLEDB determines columnt datatype through type-guessing. Appearantly, by default, OLEDB uses the first eight rows to determine the type of the column, not the cell data type as I persumed. The number of rows used to guess the data type is specified in the registry key HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows.

I found that Sharepoint does not use this typeguessing feature to determine the datatype for list columns. Sharepoint seems to use a safer approach that will fall back to either Single or Multiple lines of text if any cell in the column breaks the pattern.

Excel 2003 will also give you SmartTag validation warnings if you're opening an old Excel Workbook that contains date values in a string column or the like. In other words the datatype for the cells in Excel matter, because they will cause validation messages, but you need to clean the Excel data before importing them if you want to get the Sharepoint-list right. I'd also recommend you to select the excact cell-range when importing to avoid including some one-char strings in surrounding cells that might mess up the import.

2 Comments

  • Hi,

    I have been trying to do the same thing for the past two weeks, importing excel data to SharePoint lists. I made a small window application to upload an excel file, but i can't get it display in the SharePoint. Please guide me, or any kind of help will be appreciated. This is what i'm doing.

    private void button1_Click(object sender, System.EventArgs e)

    {

    Excel._Worksheet excelSheet = null;

    Excel.Range range;

    this.openFileDialog1.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";

    //SPWeb myWeb = SPControl.GetContextWeb(Context); //I Get error here...window application does not recognize this...



    if (this.openFileDialog1.ShowDialog() == DialogResult.OK)

    {

    try

    {

    this.excelApp = new Excel.Application();

    this.excelBook = this.excelApp.Workbooks.Open(this.openFileDialog1.FileName, true, true,

    true, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, false, true, false);

    excelSheet = (Excel._Worksheet) this.excelBook.Worksheets.get_Item(1);

    this.excelApp.Visible = true;

    this.excelApp.UserControl = true;

    range = excelSheet.get_Range("C6", "Q"+excelSheet.Rows.Count.ToString());

    Object[,] saRet = (System.Object[,])range.get_Value(Missing.Value);

    long iRows = saRet.GetUpperBound(0);

    long iCols = saRet.GetUpperBound(1);

    //MessageBox.Show("UPPER iRows="+iRows.ToString()+" iCols="+iCols.ToString());

    //MessageBox.Show("LOWER iRows="+saRet.GetLowerBound(0).ToString()+" iCols="+saRet.GetLowerBound(1).ToString());



    for (long rowCounter=1;rowCounter<=iRows;rowCounter++)

    {

    if (saRet[rowCounter, 1] == null)

    {

    MessageBox.Show("All rows have been read");

    break;

    }

    for (long colCounter=1;colCounter<=iCols;colCounter++)

    {

    if (saRet[rowCounter, colCounter] == null)

    saRet[rowCounter, colCounter] = (object ) "";

    MessageBox.Show("**"+saRet[rowCounter, colCounter].ToString()+"**");

    /*

    SPList cal ;

    cal = myWeb.Lists["Closing Calendar"];

    SPListItemCollection spcal = cal.Items;

    int totItem = cal.ItemCount;

    //output.Write("<br>"+totItem);//32 here

    foreach ( SPListItem spc in spcal)

    {

    string [] descArr = (string [])spc["Description"];

    //output.Write("<br>spl is :"+spc.ListItems.List);

    //output.Write("<br>"+SPEncode.HtmlEncode(descArr[0])); //Error here

    //output.Write("<br>"+SPEncode.HtmlEncode(spc["Item"].ToString()));//getting all items

    //output.Write("<br>"+SPEncode.HtmlEncode(spc["Date & Time"].ToString()));//Date

    //output.Write("<br>"+SPEncode.HtmlEncode(spc["CFS Product Mgrs. "].ToString()));//Error

    }

    */

    }

    }

    }

    catch( Exception theException )

    {

    String errorMessage;

    errorMessage = "Error: ";

    errorMessage = String.Concat( errorMessage, theException.Message );

    errorMessage = String.Concat( errorMessage, " Line: " );

    errorMessage = String.Concat( errorMessage, theException.Source );

    MessageBox.Show( errorMessage, "Error" );

    }//closing catch

    }//closing if

    }//closing button_click



  • What is the MOSS 2007 / WSS 3.0 server API for importing a Excel 2003 spreadsheet?

Comments have been disabled for this content.