ID CategoryID Name IsDisabled
09-00-op 11;22;33 agh 1
aaa-bb-ccc 44;55;66 agh 1
假如数据中的表信息是这样的,现在要将此表导出到Excel,ID中的内容要按“-”截取,
CategoryId 按“;”截取,变成多对多的关系,保存到Excel 里面。那么导出的结果应该是这样的。
ID CategoryID Name IsDisabled
09 11 agh 1
09 22 agh 1
09 33 agh 1
00 11 agh 1
00 22 agh 1
00 33 agh 1
op 11 agh 1
op 22 agh 1
op 33 agh 1
aaa 44 agh 1
aaa 55 agh 1
aaa 66 agh 1
bb 44 agh 1
bb 55 agh 1
bb 66 agh 1
ccc 44 agh 1
ccc 55 agh 1
ccc 66 agh 1
以下为 C#代码,很抱歉,没有写注释,如果 有需要,下次改好注释再更新, 下面的方法 有个buge,如果 遇到要 截取的那个 列的内容不符合条件,就执行不成功,这方法适合已经确定了并且统一截取格式的,
private void btnNewExprot_Click(object sender, EventArgs e)
{
SqlDataAdapter myda = new SqlDataAdapter("select * from [Exam]", conn);
DataTable dtTable = new DataTable();
myda.Fill(dtTable);
DataSet ds = new DataSet();
ds.Tables.Add(dtTable);
//循环行数
for (int rowCount = 0; rowCount < ds.Tables[0].Rows.Count; rowCount++)
{
string sqlcom = "insert into [Exam] values (";
string sqlcom1 = sqlcom;
//循环当前行列数
for (int colCount = 0; colCount < ds.Tables[0].Columns.Count; colCount++)
{
//获得当前单元格 内容
string colStr = ds.Tables[0].Rows[rowCount][colCount].ToString();
//判断当前单元格 是否 有 “-”
if (colStr.Contains("-"))
{
//如果 字符串中 有 “-” 则按照 - 进行分割成 数组
string[] bStr = colStr.Split(new char[] { '-' });
//循环 分割后的子字符串
for (int i = 0; i < bStr.Length; i++)
{
//拼接 字符串
//取得 分割后的第一个子字符串进行 拼接
sqlcom = sqlcom + "'" + bStr[i].ToString().Replace("'", "'") + "',";
//从当前行的第二个列开始
for (int colCount2 = 1; colCount2 < ds.Tables[0].Columns.Count; colCount2++)
{
string colStr2 = ds.Tables[0].Rows[rowCount][colCount2].ToString();
if (colStr2.Contains(";"))
{
string[] bStr2 = colStr2.Split(new char[] { ';' });
for (int j = 0; j < bStr2.Length; j++)
{
string sqlcom2 = sqlcom;
sqlcom = sqlcom + "'" + bStr2[j].ToString().Replace("'", "'") + "',";
for (int colCount3 = 2; colCount3 < ds.Tables[0].Columns.Count; colCount3++)
{
sqlcom = sqlcom + "'" + ds.Tables[0].Rows[rowCount][colCount3].ToString().Replace("'", "'") + "',";
}
sqlcom = sqlcom.Substring(0, sqlcom.Length - 1) + ");";
SqlCommand comm = new SqlCommand(sqlcom, conn);
sqlcom = sqlcom2;
comm.ExecuteNonQuery();
}
}
}
sqlcom = sqlcom1;
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//创建 Excel
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//创建 2个 WorkSheet
excel.SheetsInNewWorkbook = 2;
//创建WorkBook
excel.Workbooks.Add();
//获得第一个sheet
Worksheet sheet = (Worksheet)excel.ActiveWorkbook.Worksheets[1];
//给当前的sheet设置名称
sheet.Name = "第一个工作表";
//读取数据表并保存到DataSet
string sql = "select* FROM [Exam]";
SqlDataAdapter sqlDap = new SqlDataAdapter(sql, conn);
System.Data.DataTable table = new System.Data.DataTable();
sqlDap.Fill(table);
DataSet ds = new DataSet();
ds.Tables.Add(table);
//循环 行
for (int rowCount = 0; rowCount < ds.Tables[0].Rows.Count; rowCount++)
{
//循环 列
for (int colCount = 0; colCount < ds.Tables[0].Columns.Count; colCount++)
{
Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[255, 223]);
range.NumberFormatLocal = "@";
//填充单元格 内容
excel.Cells[rowCount + 2, colCount + 2] = ds.Tables[0].Rows[rowCount][colCount].ToString();
//MessageBox.Show(excel.Cells[rowCount + 1, colCount + 1].ToString());
}
}
//让 Excel 显示
//excel.Visible = true;
excel.ActiveWorkbook.SaveCopyAs(@"D:\121.xlsx");
}