Opening and saving Excel (*.xls) Files?

[EDN Admin]

Right now I have my code that opens and saves excel files but when I go to open a file that I saved it ses file in use...?

Even after the computer is restarted......?

And the code that saves the file is making the format different slightly......?

Here is the code................................>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.....

Opening the excel file....
<div style="border-right:#7f9db9 1px solid;border-top:#7f9db9 1px solid;font-size:11px;overflow:auto;border-left:#7f9db9 1px solid;line-height:100%! important;border-bottom:#7f9db9 1px solid;font-family:Courier New;background-color:white
<table style="border-top-width:0px;border-left-width:0px;margin:2px 0px;width:99%;border-bottom:#eee 0px solid;border-collapse:collapse;background-color:#fff;border-right-width:0px" cellspacing=0 cellpadding=0>
<colgroup>
<col style="padding-left:10px;font-size:11px;border-bottom:#f7f7f7 1px solid;font-family:Courier New;white-space:nowrap
<tbody>
<tr>
<td><font style="font-size:11px </font></td></tr>
<tr>
<td style="background-color:#f7f7f7          </td></tr>
<tr>
<td>           </font><font style="font-size:11px  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7       </td></tr>
<tr>
<td>      </font></td></tr>
<tr>
<td style="background-color:#f7f7f7              </td></tr>
<tr>
<td>                    try  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     {  </td></tr>
<tr>
<td>                        </font><font style="color:red this.dataGridView1.DataSource</font><font style="font-size:11px  = </font><font style="color:blue null</font><font style="font-size:11px ;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>                        this.dataGridView1.DataBindings.Clear();  </td></tr>
<tr>
<td style="background-color:#f7f7f7                         this.dataGridView1.ClearSelection();  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                         this.dataGridView1.Rows.Clear();  </td></tr>
<tr>
<td>                        this.dataGridView1.Columns.Clear();  </td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>                        OleDbConnection </font><font style="color:red cnn</font><font style="font-size:11px  = </font><font style="color:blue new</font><font style="font-size:11px  OleDbConnection("</font><font style="color:red Provider</font><font style="font-size:11px =</font><font style="color:blue Microsoft</font><font style="font-size:11px .Jet.OLEDB.4.0;Data </font><font style="color:red Source</font><font style="font-size:11px =</font><font style="color:blue " + FileName + "</font><font style="font-size:11px ;Extended </font><font style="color:red Properties</font><font style="font-size:11px ="Excel 8.0;</font><font style="color:red HDR</font><font style="font-size:11px =</font><font style="color:blue No</font><font style="font-size:11px ;</font><font style="color:red IMEX</font><font style="font-size:11px =</font><font style="color:blue 1</font><font style="font-size:11px ";");  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                         OleDbDataAdapter da;  </td></tr>
<tr>
<td>                        DataSet ds;  </td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>                        </font><font style="color:red da</font><font style="font-size:11px  = </font><font style="color:blue new</font><font style="font-size:11px  OleDbDataAdapter("Select * from [" + SheetName1 + "]", cnn);  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                         </font><font style="color:red ds</font><font style="font-size:11px  = </font><font style="color:blue new</font><font style="font-size:11px  DataSet("TestExcel");  </font></td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                         da.Fill(ds, "TestExcel");  </td></tr>
<tr>
<td>                        </font><font style="color:red dataGridView1.DataSource</font><font style="font-size:11px  = </font><font style="color:blue ds</font><font style="font-size:11px .Tables[0];  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                     }  </td></tr>
<tr>
<td>                    catch (Exception ex)  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     {  </td></tr>
<tr>
<td>                        MessageBox.Show(ex.Message);  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     }  </td></tr>
<tr>
<td>                    </font></td></tr>
<tr>
<td style="background-color:#f7f7f7              </td></tr>
<tr>
<td>             </td></tr>
<tr>
<td style="background-color:#f7f7f7                </td></tr>
<tr>
<td>          </td></tr>
<tr>
<td style="background-color:#f7f7f7             } </td></tr></tbody></table>
Saving the Excel File...
<div style="border-right:#7f9db9 1px solid;border-top:#7f9db9 1px solid;font-size:11px;overflow:auto;border-left:#7f9db9 1px solid;line-height:100%! important;border-bottom:#7f9db9 1px solid;font-family:Courier New;background-color:white
<table style="border-top-width:0px;border-left-width:0px;margin:2px 0px;width:99%;border-bottom:#eee 0px solid;border-collapse:collapse;background-color:#fff;border-right-width:0px" cellspacing=0 cellpadding=0>
<colgroup>
<col style="padding-left:10px;font-size:11px;border-bottom:#f7f7f7 1px solid;font-family:Courier New;white-space:nowrap
<tbody>
<tr>
<td><font style="font-size:11px public void SaveAs()  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7         {  </td></tr>
<tr>
<td>            using (SaveFileDialog </font><font style="color:red saveFileDialog1</font><font style="font-size:11px  = </font><font style="color:blue GetExcelSaveFileDialog</font><font style="font-size:11px ())  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7             {  </td></tr>
<tr>
<td>                </font><font style="color:red saveFileDialog1.FileName</font><font style="font-size:11px  = </font><font style="color:blue SourceWords</font><font style="font-size:11px ;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                 if (saveFileDialog1.ShowDialog(this) == DialogResult.OK)  </td></tr>
<tr>
<td>                {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     </font><font style="color:red FileName</font><font style="font-size:11px  = </font><font style="color:blue saveFileDialog1</font><font style="font-size:11px .FileName;  </font></td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                     Workbook </font><font style="color:red workbook</font><font style="font-size:11px  = </font><font style="color:blue ExcelGenerator</font><font style="font-size:11px .Generate(this.dataGridView1);  </font></td></tr>
<tr>
<td>                    workbook.Save(FileName);  </td></tr>
<tr>
<td style="background-color:#f7f7f7                    </font></td></tr>
<tr>
<td>                </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                   </font></td></tr>
<tr>
<td>                }  </td></tr>
<tr>
<td style="background-color:#f7f7f7             }  </td></tr>
<tr>
<td>        } </td></tr></tbody></table> Attachement that the save file uses.....
<div style="border-right:#7f9db9 1px solid;border-top:#7f9db9 1px solid;font-size:11px;overflow:auto;border-left:#7f9db9 1px solid;line-height:100%! important;border-bottom:#7f9db9 1px solid;font-family:Courier New;background-color:white
<table style="border-top-width:0px;border-left-width:0px;margin:2px 0px;width:99%;border-bottom:#eee 0px solid;border-collapse:collapse;background-color:#fff;border-right-width:0px" cellspacing=0 cellpadding=0>
<colgroup>
<col style="padding-left:10px;font-size:11px;border-bottom:#f7f7f7 1px solid;font-family:Courier New;white-space:nowrap
<tbody>
<tr>
<td><font style="font-size:11px using System;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7 using System.Collections.Generic;  </td></tr>
<tr>
<td>using System.Text;  </td></tr>
<tr>
<td style="background-color:#f7f7f7 using CarlosAg.ExcelXmlWriter;  </td></tr>
<tr>
<td>using System.Windows.Forms;  </td></tr>
<tr>
<td style="background-color:#f7f7f7 using System.Drawing;  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7 namespace DynamicData  </td></tr>
<tr>
<td>{  </td></tr>
<tr>
<td style="background-color:#f7f7f7     public static class ExcelGenerator  </td></tr>
<tr>
<td>    {  </td></tr>
<tr>
<td style="background-color:#f7f7f7         public static Workbook Generate(DataGridView dataGridView)  </td></tr>
<tr>
<td>        {  </td></tr>
<tr>
<td style="background-color:#f7f7f7             Workbook </font><font style="color:red workbook</font><font style="font-size:11px  = </font><font style="color:blue new</font><font style="font-size:11px  Workbook();  </font></td></tr>
<tr>
<td>            Worksheet </font><font style="color:red worksheet</font><font style="font-size:11px  = </font><font style="color:blue workbook</font><font style="font-size:11px .Worksheets.Add("Sheet 1");  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>            WorksheetRow </font><font style="color:red worksheetRow</font><font style="font-size:11px  = </font><font style="color:blue new</font><font style="font-size:11px  WorksheetRow();  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7             foreach (DataGridViewColumn dataGridViewColumn in dataGridView.Columns)  </td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 worksheet.Table.Columns.Add(new WorksheetColumn(dataGridViewColumn.Width));  </td></tr>
<tr>
<td>                worksheetRow.Cells.Add(new WorksheetCell(dataGridViewColumn.HeaderText));  </td></tr>
<tr>
<td style="background-color:#f7f7f7             }  </td></tr>
<tr>
<td>            worksheet.Table.Rows.Insert(0, worksheetRow);  </td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>            WorksheetStyle </font><font style="color:red worksheetDefaultStyle</font><font style="font-size:11px  = </font><font style="color:blue GetWorksheetStyle</font><font style="font-size:11px (dataGridView.DefaultCellStyle, "Default");  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7             workbook.Styles.Add(worksheetDefaultStyle);  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7             for (int </font><font style="color:red rowIndex</font><font style="font-size:11px  = </font><font style="color:blue 0</font><font style="font-size:11px ; rowIndex </font><font style="color:blue <</font><font style="font-size:11px  </font><font style="font-weight:bold;color:black dataGridView.RowCount</font><font style="font-size:11px ; ++rowIndex)  </font></td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 </font><font style="color:blue worksheet</font><font style="font-size:11px </font><font style="color:red worksheetRow</font><font style="font-size:11px  = worksheet.Table.Rows.Add();  </font></td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                 for (int </font><font style="color:red columnIndex</font><font style="font-size:11px  = </font><font style="color:blue 0</font><font style="font-size:11px ; columnIndex </font><font style="color:blue <</font><font style="font-size:11px  </font><font style="font-weight:bold;color:black dataGridView.ColumnCount</font><font style="font-size:11px ; ++columnIndex)  </font></td></tr>
<tr>
<td>                {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     DataGridViewCell </font><font style="color:red cell</font><font style="font-size:11px  = </font><font style="color:blue dataGridView</font><font style="font-size:11px [columnIndex, rowIndex];  </font></td></tr>
<tr>
<td>                    WorksheetStyle </font><font style="color:red cellStyle</font><font style="font-size:11px  = </font><font style="color:blue GetWorksheetStyle</font><font style="font-size:11px (cell.InheritedStyle, "column" + columnIndex + "row" + rowIndex);  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>                    if (cellStyle != null)  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     {  </td></tr>
<tr>
<td>                        workbook.Styles.Add(cellStyle);  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     }  </td></tr>
<tr>
<td>                    else  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     {  </td></tr>
<tr>
<td>                        </font><font style="color:red cellStyle</font><font style="font-size:11px  = </font><font style="color:blue worksheetDefaultStyle</font><font style="font-size:11px ;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                     }  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                     DataType </font><font style="color:red dataType</font><font style="font-size:11px  = </font><font style="color:blue GetDataType</font><font style="font-size:11px (cell.ValueType);  </font></td></tr>
<tr>
<td>                    worksheetRow.Cells.Add(cell.FormattedValue.ToString(), dataType, cellStyle.ID);  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 }  </td></tr>
<tr>
<td>            }  </td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>            return workbook;  </td></tr>
<tr>
<td style="background-color:#f7f7f7         }  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7         private static WorksheetStyle GetWorksheetStyle(DataGridViewCellStyle dataGridViewCellStyle, string id)  </td></tr>
<tr>
<td>        {  </td></tr>
<tr>
<td style="background-color:#f7f7f7             WorksheetStyle </font><font style="color:red worksheetStyle</font><font style="font-size:11px  = </font><font style="color:blue null</font><font style="font-size:11px ;  </font></td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7             if (dataGridViewCellStyle != null)  </td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 </font><font style="color:red worksheetStyle</font><font style="font-size:11px  = </font><font style="color:blue new</font><font style="font-size:11px  WorksheetStyle(id);  </font></td></tr>
<tr>
<td>                if (!dataGridViewCellStyle.BackColor.IsEmpty)  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 {  </td></tr>
<tr>
<td>                    </font><font style="color:red worksheetStyle.Interior.Color</font><font style="font-size:11px  = </font><font style="color:blue GetColorName</font><font style="font-size:11px (dataGridViewCellStyle.BackColor);  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                     </font><font style="color:red worksheetStyle.Interior.Pattern</font><font style="font-size:11px  = </font><font style="color:blue StyleInteriorPattern</font><font style="font-size:11px .Solid;  </font></td></tr>
<tr>
<td>                }  </td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>                if (!dataGridViewCellStyle.ForeColor.IsEmpty)  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 {  </td></tr>
<tr>
<td>                    </font><font style="color:red worksheetStyle.Font.Color</font><font style="font-size:11px  = </font><font style="color:blue GetColorName</font><font style="font-size:11px (dataGridViewCellStyle.ForeColor);  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                 }  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                 if (dataGridViewCellStyle.Font != null)  </td></tr>
<tr>
<td>                {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                     </font><font style="color:red worksheetStyle.Font.Bold</font><font style="font-size:11px  = </font><font style="color:blue dataGridViewCellStyle</font><font style="font-size:11px .Font.Bold;  </font></td></tr>
<tr>
<td>                    </font><font style="color:red worksheetStyle.Font.FontName</font><font style="font-size:11px  = </font><font style="color:blue dataGridViewCellStyle</font><font style="font-size:11px .Font.Name;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                     </font><font style="color:red worksheetStyle.Font.Italic</font><font style="font-size:11px  = </font><font style="color:blue dataGridViewCellStyle</font><font style="font-size:11px .Font.Italic;  </font></td></tr>
<tr>
<td>                    </font><font style="color:red worksheetStyle.Font.Size</font><font style="font-size:11px  = (int)dataGridViewCellStyle.Font.Size;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                     </font><font style="color:red worksheetStyle.Font.Strikethrough</font><font style="font-size:11px  = </font><font style="color:blue dataGridViewCellStyle</font><font style="font-size:11px .Font.Strikeout;  </font></td></tr>
<tr>
<td>                    </font><font style="color:red worksheetStyle.Font.Underline</font><font style="font-size:11px  = </font><font style="color:blue dataGridViewCellStyle</font><font style="font-size:11px .Font.Underline ? UnderlineStyle.Single : UnderlineStyle.None;  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7                 }  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7                 worksheetStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "Black");  </td></tr>
<tr>
<td>                worksheetStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "Black");  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 worksheetStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "Black");  </td></tr>
<tr>
<td>                worksheetStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "Black");  </td></tr>
<tr>
<td style="background-color:#f7f7f7             }  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7             return worksheetStyle;  </td></tr>
<tr>
<td>        }  </td></tr>
<tr>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td>        private static string GetColorName(Color color)  </td></tr>
<tr>
<td style="background-color:#f7f7f7         {  </td></tr>
<tr>
<td>            return "#" + color.ToArgb().ToString("X").Substring(2);  </td></tr>
<tr>
<td style="background-color:#f7f7f7         }  </td></tr>
<tr>
<td> </td></tr>
<tr>
<td style="background-color:#f7f7f7         private static DataType GetDataType(Type valueType)  </td></tr>
<tr>
<td>        {  </td></tr>
<tr>
<td style="background-color:#f7f7f7             if (</font><font style="color:red valueType</font><font style="font-size:11px  == typeof(DateTime))  </font></td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 return DataType.String;  </td></tr>
<tr>
<td>            }  </td></tr>
<tr>
<td style="background-color:#f7f7f7             else if (</font><font style="color:red valueType</font><font style="font-size:11px  == typeof(string))  </font></td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 return DataType.String;  </td></tr>
<tr>
<td>            }  </td></tr>
<tr>
<td style="background-color:#f7f7f7             else if (</font><font style="color:red valueType</font><font style="font-size:11px  == typeof(sbyte)  </font></td></tr>
<tr>
<td>              || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(byte)  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7               || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(short)  </font></td></tr>
<tr>
<td>              || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(ushort)  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7               || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(int)  </font></td></tr>
<tr>
<td>              || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(uint)  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7               || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(long)  </font></td></tr>
<tr>
<td>              || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(ulong)  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7               || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(float)  </font></td></tr>
<tr>
<td>              || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(double)  </font></td></tr>
<tr>
<td style="background-color:#f7f7f7               || </font><font style="color:red valueType</font><font style="font-size:11px  == typeof(decimal))  </font></td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 return DataType.Number;  </td></tr>
<tr>
<td>            }  </td></tr>
<tr>
<td style="background-color:#f7f7f7             else  </td></tr>
<tr>
<td>            {  </td></tr>
<tr>
<td style="background-color:#f7f7f7                 return DataType.String;  </td></tr>
<tr>
<td>            }  </td></tr>
<tr>
<td style="background-color:#f7f7f7         }  </td></tr>
<tr>
<td>    }  </td></tr>
<tr>
<td style="background-color:#f7f7f7 }  </td></tr>
<tr>
<td> </td></tr></tbody></table>

Does anybody have a better way to do this with excel for C# only &&&&&& one that works....

Everything that Ive found on the internet so far is VB code or HTML code and its very bothering....

(This is using windows form with a datagrid view....)

I appreciate any help,

Thanks,

zBuster

View the full article