当我从数据表复制值到Excel表格时,前导零被丢失了。这可能是因为Excel将这些值视为数字而不是文本。
我是这样复制这些值的:
myWorksheet.Cells[i + 2, j] = dtCustomers.Rows[i][j - 1].ToString();
如何将整个列或每个单元格格式设置为文本?
相关问题,如何将myWorksheet.Cells[i + 2, j]
转换为在智能感知中显示样式属性?
当我从数据表复制值到Excel表格时,前导零被丢失了。这可能是因为Excel将这些值视为数字而不是文本。
我是这样复制这些值的:
myWorksheet.Cells[i + 2, j] = dtCustomers.Rows[i][j - 1].ToString();
如何将整个列或每个单元格格式设置为文本?
相关问题,如何将myWorksheet.Cells[i + 2, j]
转换为在智能感知中显示样式属性?
以下是一些代码,用于在SpreadsheetGear for .NET中将列A和列C格式化为文本。该API类似于Excel,但SpreadsheetGear通常更强类型。应该不难想出如何将其转换为适用于Excel/COM的代码:
IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.Worksheets[0].Cells;
// Format column A as text.
cells["A:A"].NumberFormat = "@";
// Set A2 to text with a leading '0'.
cells["A2"].Value = "01234567890123456789";
// Format column C as text (SpreadsheetGear uses 0 based indexes - Excel uses 1 based indexes).
cells[0, 2].EntireColumn.NumberFormat = "@";
// Set C3 to text with a leading '0'.
cells[2, 2].Value = "01234567890123456789";
workbook.SaveAs(@"c:\tmp\TextFormat.xlsx", FileFormat.OpenXMLWorkbook);
声明:我拥有SpreadsheetGear LLC
如果您在添加带有前导零的数字值之前将单元格格式设置为文本,则不必添加撇号即可保留前导零。如果您尝试手动向Excel中的默认表添加前导零值,然后将其转换为文本,则前导零将被删除。如果您先将单元格转换为文本,然后再添加值,则可以正常显示。编程时也适用同样的原则。
// Pull in all the cells of the worksheet
Range cells = xlWorkBook.Worksheets[1].Cells;
// set each cell's format to Text
cells.NumberFormat = "@";
// reset horizontal alignment to the right
cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
// now add values to the worksheet
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value.ToString();
}
}
以下是我在使用Excel Interop时发现有效的解决方案:
myWorksheet.Columns[j].NumberFormat = "@"; // column as a text
myWorksheet.Cells[i + 2, j].NumberFormat = "@"; // cell as a text
在放置数据到Excel之前,应该运行这段代码。 行和列的编号是从1开始计数的。
更详细的说明。尽管具有SpreadsheetGear参考资料的已接受响应看起来几乎正确,但我对其有两个关注点:
在写入 Excel 之前,需要更改格式:
xlApp = New Excel.Application
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
Dim cells As Excel.Range = xlWorkSheet.Cells
'set each cell's format to Text
cells.NumberFormat = "@"
'reset horizontal alignment to the right
cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
我最近也遇到了这个问题,并且从上述建议中学到了两件事情。
误导人的是,现在单元格中有一个不同的值。幸运的是,当您复制/粘贴或导出到CSV时,撇号不会被包含。
结论:使用撇号而不是numberFormatting以保留前导零。
rng.NumberFormat = "@"; rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
;或使用此处找到的其他对齐方法:
[链接]http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(MICROSOFT.OFFICE.INTEROP.EXCEL.RANGE.HORIZONTALALIGNMENT);k(TargetFrameworkMoniker-%22.NETFRAMEWORK%2cVERSION%3dV4.0%22);k(DevLang-CSHARP)&rd=true - TedWorkSheet.Columns.NumberFormat
,并将其设置为string "@"
,以下是示例:Excel._Worksheet workSheet = (Excel._Worksheet)_Excel.Worksheets.Add();
//set columns format to text format
workSheet.Columns.NumberFormat = "@";
workSheet.Columns[0].NumberFormat = "@";
workSheet.get_Range("A1", "D1").NumberFormat = "@";
if (dtCustomers.Columns[j - 1].DataType != typeof(decimal) && dtCustomers.Columns[j - 1].DataType != typeof(int))
{
myWorksheet.Cells[i + 2, j].NumberFormat = "@";
}
Range.NumberFormat = "@"
只能部分解决问题:
//where [1] - column number which you want to make text
ExcelWorksheet.Columns[1].NumberFormat = "@";
//If you want to format a particular column in all sheets in a workbook - use below code. Remove loop for single sheet along with slight changes.
//path were excel file is kept
string ResultsFilePath = @"C:\\Users\\krakhil\\Desktop\\TGUW EXCEL\\TEST";
Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(ResultsFilePath);
ExcelApp.Visible = true;
//Looping through all available sheets
foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
{
//Selecting the worksheet where we want to perform action
ExcelWorksheet.Select(Type.Missing);
ExcelWorksheet.Columns[1].NumberFormat = "@";
}
//saving excel file using Interop
ExcelWorkbook.Save();
//closing file and releasing resources
ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(ExcelWorkbook);
ExcelApp.Quit();
Marshal.FinalReleaseComObject(ExcelApp);
ExcelWorksheet.Columns[1].NumberFormat = "@";
- KR Akhil