从SQL Server 2008数据库使用c导出excel表格
问题描述:
我想从SQL Server 2008数据库导出一个表格到Excel表格,其数据库模式中的约束条件相同。 例如,如果在数据库中某个列的数据类型为integer或varchar ,那么在Excel表格中它应该具有相同的数据类型,并且如果有人试图将无效数据输入到Excel表格中,则应该在Excel表格本身中显示一条错误消息列。从SQL Server 2008数据库使用c导出excel表格
我发现了一个代码脚本here但它没有实现约束。
答
//这是我的代码,我已经使用了SQL服务器到Excel数据库转储 //试试看
private void button1_Click_2(object sender, EventArgs e)
{
DataSet DtSetmatch = new DataSet();
SQL.DataTable dttt = new SQL.DataTable();
string selectedTable = cmbImportItemList.Text;
using (SqlDataAdapter adater = new SqlDataAdapter("Select * from "+selectedTable, new SqlConnection(Properties.Settings.Default.connectionstring2)))
{
adater.Fill(dttt);
}
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
SQL.DataTable dtCategories = dttt.DefaultView.ToTable(true,dttt.Columns[0].ToString());
foreach (SQL.DataRow category in dtCategories.Rows)
{
oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
oSheet.Name = category[0].ToString()
.Replace(" ", "")
.Replace(" ", "")
.Replace("/", "")
.Replace("\\", "")
.Replace("*", "");
string[] colNames = new string[dttt.Columns.Count];
int col = 0;
try
{
foreach (SQL.DataColumn dc in dttt.Columns) colNames[col++] = dc.ColumnName;////dc.ColumnName;
char lastColumn = (char)(65 + dttt.Columns.Count - 1);
oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
SQL.DataRow[] dr = dttt.Select(string.Format(""));
string[,] rowData = new string[dr.Count<SQL.DataRow>(), dttt.Columns.Count];
int rowCnt = 0;
int redRows = 2;
foreach (SQL.DataRow row in dr)
{
for (col = 0; col < dttt.Columns.Count; col++)
{
rowData[rowCnt, col] = row[col].ToString();
}
redRows++;
rowCnt++;
}
oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value2 = rowData;
oXL.Visible = true;
oXL.UserControl = true;
try
{
oWB.SaveAs("C://Products.xlsx", AccessMode: Excel.XlSaveAsAccessMode.xlShared);
}
catch
{
}
break;
}
catch (Exception aee)
{
}
break;
}
}
这听起来像一个大项目...... – 2012-04-10 14:13:09
Excel没有数据类型的概念除了以特定方式格式化单元格/列。您可以尝试创建一个尝试将输入与格式匹配的脚本,如果它不匹配,则会使单元格变为红色。或删除内容或任何似乎是正确的 – Sascha 2012-04-11 03:28:56