sqlserver中65535_excel数据导入SqlServer中

// 遍历数据表中的所有列

foreach (DataGridViewColumn cs in TasksGridView.Columns)

{

// 假如并不想把主键也显示出来

if (cs.HeaderText != "编号")

{

ws.Cells[row, cell] = cs.HeaderText;

r = (Range)ws.Cells[row, cell];

ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

//此处用来设置列的样式

cell++;

}

}

// 创建行,把数据视图记录输出到对应的Excel 单元格

for (int i = 2; i &lt TasksData.Tables[0].Rows.Count; i++)

{

for (int j = 1; j &lt TasksData.Tables[0].Columns.Count; j++)

{

ws.Cells[i, j] = TasksData.Tables[0].Rows[j].ToString();

// r = (Range)ws.Cells[i,j];

Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]);

rg.EntireColumn.ColumnWidth = 20;

// rg.Columns.AutoFit();

rg.NumberFormatLocal = "@";

}

}

#endregion

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

//显示 Excel

excelApp.Visible = true;

}

private void button5_Click(object sender, EventArgs e)

{

DataSet ds = Bind();

dataGridView1.DataSource = ds.Tables[0];

}

private DataSet Bind()

{

SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI");

SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn);

DataSet ds = new DataSet();

da.Fill(ds);

return ds;

}

private void button2_Click(object sender, EventArgs e)

{

SaveFileDialog sfd = new SaveFileDialog();

sfd.Title = "请选择将导出的EXCEL文件存放路径";

sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息";

sfd.Filter = "Excel文档(*.xls)|*.xls";

sfd.ShowDialog();

if (sfd.FileName != "")

{

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

if (excelApp == null)

{

MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");

}

else

{

Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;

Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

Microsoft.Office.Interop.Excel.Worksheet worksheet =(Worksheet) workbook.Worksheets[1];

DataSet ds=Bind();

for (int i = 1; i &lt ds.Tables[0].Rows.Count; i++)

{

for (int j = 1; j &lt ds.Tables[0].Columns.Count;j++ )

{

if (i == 1)

{

worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText;

}

worksheet.Cells[i+1, j] = ds.Tables[0].Rows[j].ToString();

}

}

//保存方式一：保存WorkBook

//workbook.SaveAs(@"F:\CData.xls",

// Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,

// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,

// Missing.Value,Missing.Value);

//保存方式二：保存WorkSheet

// worksheet.SaveAs(@"F:\CData2.xls",

// Missing.Value, Missing.Value, Missing.Value, Missing.Value,

// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Valu