请教高人难题！C# 导出 Excel ，如何单元格 设置 格式（以数据流的形式写出Excel）急！！
将 DataGridView 数据 导出 Excel （以数据流的形式写出Excel）
但是文本类型的数字 导出 却是 数值型了，身份证、卡号等就是错误的了，因为数据太长。
请教高人，有什么好方法解决这个问题，着急！！！以下是代码：
public static void ExportDataGridViewToExcel(DataGridView MyDataGridView)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出Excel文件到";
if (saveFileDialog.ShowDialog() != DialogResult.Cancel)
{
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
string str = "";
try
{
//写标题
for (int i = 0; i < MyDataGridView.ColumnCount; i++)
{
if (MyDataGridView.Columns[i].Visible == true)
{
if (i > 0)
{
str += "\t";
}
str += MyDataGridView.Columns[i].HeaderText;
}
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < MyDataGridView.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < MyDataGridView.Columns.Count; k++)
{
if (MyDataGridView.Columns[k].Visible == true)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr +=MyDataGridView.Rows[j].Cells[k].Value.ToString();
}
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
[解决办法]
public void ExportExcel(DataSet tempds, string saveFileName)
{
if (tempds == null)
{
MessageBox.Show("要导出的数据为空！！！");
this.Close();
return;
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
this.Close();
return;
}
try
{
xlApp.Visible = false;
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
this.Close();
return;
}
bool fileSaved = false;
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range;
long totalCount = tempds.Tables[0].Rows.Count;
long rowRead = 0;
float percent = 0;
for (int i = 0; i < tempds.Tables[0].Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = tempds.Tables[0].Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
int[] stringFlags = new int[60];
for (int r = 0; r < tempds.Tables[0].Rows.Count; r++)
{
for (int i = 0; i < tempds.Tables[0].Columns.Count; i++)//判断有多少是字符类型
{
if (r == 0)
{
string type = tempds.Tables[0].Columns[i].DataType.ToString();
if (type == "System.String")
{
stringFlags[i] = 1;
range = (Excel.Range)worksheet.Cells[r + 3, i + 1];
range.NumberFormatLocal = "@";
}
worksheet.Cells[r + 3, i + 1] = tempds.Tables[0].Rows[r][i];
}
else
{
if (stringFlags[i] == 1)//对于字符类型的处理
{
range = (Excel.Range)worksheet.Cells[r + 3, i + 1];
range.NumberFormatLocal = "@";
}
worksheet.Cells[r + 3, i + 1] = tempds.Tables[0].Rows[r][i];
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
lbInfo.Text = "正在导出数据[" + percent.ToString("0.00") + "%]...";
Application.DoEvents();
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
}
catch(Exception ee)
{
MessageBox.Show("导出发生错误，错误信息为："+ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
this.Close();
}
}
------解决方案--------------------
- C# code
namespace ExportToExcel{ using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; using System.Runtime.InteropServices; /*********************************************************************************** ****Class Name : ExcelManger ****Author: KingNa ****Create Date : 2006-9-1 ****CopyRight: Reserve this info if you want to User this Class ***********************************************************************************/ public class ExcelManager:IDisposable { Excel.Range m_objRange = null; Excel.Application m_objExcel = null; Excel.Workbooks m_objBooks = null; Excel._Workbook m_objBook = null; Excel.Sheets m_objSheets = null; Excel._Worksheet m_objSheet = null; Excel.QueryTable m_objQryTable = null; object m_objOpt = System.Reflection.Missing.Value; //DataBase-used variable private System.Data.SqlClient.SqlConnection sqlConn = null; private string strConnect = string.Empty; private System.Data.SqlClient.SqlCommand sqlCmd = null; //Sheets variable private double dbSheetSize = 65535;//the hight limit number in one sheet private int intSheetTotalSize = 0;//total record can divied sheet number private double dbTotalSize = 0;//record total number /// <summary> /// 建构函数 /// </summary> public ExcelManager(){} /// <summary> /// 建构函数 /// </summary> /// <param name="dbHL">一个Excel表格的最大记录数</param> /// <param name="dbTotal">该数据库表共查询出多少条记录</param> /// <param name="intDivide">查询出的记录可分成几个Excel</param> /// <param name="conn">sqlConnection</param> public ExcelManager(Double dbHL,Double dbTotal,int intDivide,SqlConnection conn ) { dbSheetSize = dbHL; intSheetTotalSize = intDivide; dbTotalSize = dbTotal; sqlConn = conn; } /// <summary> /// 建构函数 /// </summary> /// <param name="dbHL">一个Excel表格的最大记录数</param> /// <param name="strTableName">需查询的数据库的表名</param> /// <param name="conn">sqlConnection</param> public ExcelManager(Double dbHL,string strTableName,SqlConnection conn) { dbSheetSize = dbHL; sqlConn = conn; intSheetTotalSize = GetTotalSize(strTableName,sqlConn); } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } private void Dispose(bool disposing) { if(disposing) { // Dispose managed resources. Marshal.FinalReleaseComObject(m_objExcel); m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBooks = null; m_objBook = null; m_objExcel = null; } }