I use Spire.XLS because Interop makes many problems. I convert DataGrid into DataTable and i export it i new file, This file include many numbers ith dot and these numbers are false. It's a problem of format or just programming ?
When i create a new workbook too, i have 3 worksheet by default with another worksheet named : " Evaluation Warning ". Can i remove these Worksheets ?
- Code: Select all
private void ExportToExcel()
{
dg_test.SelectAllCells(); // Sélection du DataGrid
dg_test.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, dg_test); // Copie presse-papier
dg_test.UnselectAllCells();
String result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
// Conversion DataGrid en DataTable avec la virgule comme séparation
string[] Lines = result.Split(new string[] { "\r\n", "\n" }, StringSplitOptions.None);
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToUpper(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0) - 1; i++)
{
Fields = Lines[i].Split(new char[] { ',' });
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
{
Row[f] = Fields[f];
}
dt.Rows.Add(Row);
}
//fin de la conversion
//Propriétés pour le SaveFileDialog
SaveFileDialog savefiledlg1 = new SaveFileDialog();
savefiledlg1.Filter = "xls(*.xls*) |*.xls* | Classeur Excel(*.xlsx) |*.xlsx | Tous(*.*)|*.* | CSV (séparateur: point-virgule) (*.csv)|*.csv* ";
savefiledlg1.RestoreDirectory = true;
savefiledlg1.DefaultExt = ".xlsx";
savefiledlg1.AddExtension = true;
savefiledlg1.InitialDirectory = "C:\\Users\\"+ System.Environment.UserName + "\\Desktop";
savefiledlg1.Title = "Sauvegardez votre fichier Excel";
if (savefiledlg1.ShowDialog() == DialogResult.OK)
{
Sp.Workbook wourbouk = new Sp.Workbook();
wourbouk.Version = Sp.ExcelVersion.Version2013;
Sp.Worksheet shit = wourbouk.Worksheets[0];
shit.InsertDataTable(dt, true, 1,1); // Methode Spite.Xls pour inésrer une DataTable dans un fichier Excel
wourbouk.SaveToFile(savefiledlg1.FileName, Sp.ExcelVersion.Version2013);
System.Windows.MessageBox.Show("Le DataGrid a été inséré avec succès dans le fichier Excel ,à l'adresse suivante :" + savefiledlg1.FileName + ".");
System.Diagnostics.Process.Start(savefiledlg1.FileName);
}
else
{
MessageBox.Show("Une erreur inconnue est survenue ou vous avez annulé l'opération de sauvegarde");
}
}
This is my function for export DataTable to Excel
And the problem met for excel file
I want to insert an image but i'm not allowed
Sorry for my bad english.