将Excel列设置为文本(将DataGridView中的数据导出到Excel) [英] Set Excel Column to Text (Exporting Data in DataGridView to Excel)
本文介绍了将Excel列设置为文本(将DataGridView中的数据导出到Excel)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
- 在填写DataGridView之后格式化DataTable中的列。
- 将数据从数据库中提取为VARCHAR。 / li>
- 在我的方法中使用Range.EntireColumn,Range.Cells.NumberFormat和其他方式。
这些都不行!
在导出为excel并保存文件的代码下面
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelLibro = ExcelApp.Workbooks.Add(misDatos);
ExcelHoja =(Excel.Worksheet)ExcelLibro.Worksheets.get_Item(1);
Excel.Range rangoHoja =(Excel.Range)ExcelHoja.Cells [1,1];
rangoHoja.Select();
ExcelHoja.PasteSpecial(rangoHoja,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,true);
ExcelLibro.SaveAs(opcionSFD.FileName,
Excel.XlFileFormat.xlWorkbookNormal,
misDatos,
misDatos,
misDatos,
misDatos,
Excel.XlSaveAsAccessMode.xlExclusive,
misDatos,
misDatos,
misDatos,
misDatos,
misDatos);
ExcelApp.DisplayAlerts = true;
ExcelLibro.Close(true,misDatos,misDatos);
ExcelApp.Quit();
我可以做什么?
解决方案
这对我有用:
在调用 ExcelHoja.PasteSpecial(...)
添加以下行:
rangoHoja.NumberFormat =@;
将单元格的格式设置为文本。
...
rangoHoja.Select();
rangoHoja.NumberFormat =@; //已添加行
ExcelHoja.PasteSpecial(...);
...
希望这有帮助
I'm exporting data from DataGridView to Excel. Everything works great but when a data in columns have, for example: 000115455, Excel is ignoring the '0' and inserting 115455. I try next:
- Format the column in DataTable before Fill DataGridView
- Casting data retrieve from database as VARCHAR.
- Format the column in DataGridView after Fill.
- Using Range.EntireColumn, Range.Cells.NumberFormat and other ways in my method.
None of this works!
Below the piece of code that export to excel and save the file
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelLibro = ExcelApp.Workbooks.Add(misDatos);
ExcelHoja = (Excel.Worksheet)ExcelLibro.Worksheets.get_Item(1);
Excel.Range rangoHoja = (Excel.Range)ExcelHoja.Cells[1, 1];
rangoHoja.Select();
ExcelHoja.PasteSpecial(rangoHoja, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
ExcelLibro.SaveAs(opcionSFD.FileName,
Excel.XlFileFormat.xlWorkbookNormal,
misDatos,
misDatos,
misDatos,
misDatos,
Excel.XlSaveAsAccessMode.xlExclusive,
misDatos,
misDatos,
misDatos,
misDatos,
misDatos);
ExcelApp.DisplayAlerts = true;
ExcelLibro.Close(true, misDatos, misDatos);
ExcelApp.Quit();
What I can do?
解决方案
This works for me:
Before call ExcelHoja.PasteSpecial(...)
add this line:
rangoHoja.NumberFormat = "@";
This sets cell's format to Text.
...
rangoHoja.Select();
rangoHoja.NumberFormat = "@"; //Added Line
ExcelHoja.PasteSpecial(...);
...
Hope this helps
这篇关于将Excel列设置为文本(将DataGridView中的数据导出到Excel)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文