在工作中,会遇到这样的需求,选取某一年,导出选择年份的月度、季度或者年度报表,月度报表不可能产生12个单独的Excel,这样的话客户可能会和你拼命,将12个月份分为12个Sheet放在一个Excel里面,可能客户会请你吃饭。
目前的做法是根据选择的年份,将该年份所有的数据全部获取到,根据表的类型(几个Sheet)分为多个DataTable放到DataSet里面,接着根据DataSet.Tables的数量导出相应数量Sheet的Excel。
步骤一:查询数据(代码略)
步骤二:分解DataTable(代码段一)
步骤三:导出Excel(代码段二)
附:单Sheet导出Excel(代码段三)
代码段一:
/// <summary>
/// 将DataTable里面的数据按 type 规则分为一定数量的DataTable 放到DataSet里
/// </summary>
/// <param name="ds">目标存储</param>
/// <param name="dt">源数据</param>
/// <param name="type">规则类型</param>
private void Classification(DataSet ds,DataTable dt, string type)
{
if (type == "月度报表")
{
for (int i = 0; i < 12; i++)
{
DataTable table = new DataTable();
table = dt.Clone();
table.TableName = "dt" + i;
ds.Tables.Add(table);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < 12; j++)
{
if ((int)dt.Rows[i]["TB_I_Month"] == j + 1)
{
ds.Tables[j].Rows.Add(dt.Rows[i].ItemArray);
continue;
}
}
}
for (int i = 0; i < 12; i++)
{
ds.Tables[i].Columns.Remove(ds.Tables[i].Columns["TB_I_Month"]);
}
}
if (type == "季度报表")
{
for (int i = 0; i < 4; i++)
{
DataTable table = new DataTable();
table = dt.Clone();
table.TableName = "dt" + i;
ds.Tables.Add(table);
}
int index = 0;
for (int j = 0; j < 12; j += 3)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if ((int)dt.Rows[i]["TB_I_Month"] == j + 1 || (int)dt.Rows[i] ["TB_I_Month"] == j + 2 || (int)dt.Rows[i]["TB_I_Month"] == j + 3)
{
ds.Tables[index].Rows.Add(dt.Rows[i].ItemArray);
}
}
index++;
}
for (int i = 0; i < 4; i++)
{
ds.Tables[i].Columns.Remove(ds.Tables[i].Columns["TB_I_Month"]);
}
}
if (type == "年度报表")
{
DataTable table = new DataTable();
table = dt.Clone();
table.TableName = "dt";
ds.Tables.Add(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
ds.Tables[0].Rows.Add(dt.Rows[i].ItemArray);
}
ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["TB_I_Month"]);
}
}
代码段二:
/// <summary>
/// 导出多 Sheet 报表
/// </summary>
/// <param name="ds">要导出的数据</param>
public void DataToExcle(System.Data.DataSet ds)
{
if (ds.Tables == null||ds.Tables.Count==0)
{
MessageBox.Show("无数据!");
return;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xlsx";
saveFileDialog.Title = "导出数据到Execl表格";
if (ds.Tables.Count == 12)
saveFileDialog.FileName = "月度报表";
if (ds.Tables.Count == 4)
saveFileDialog.FileName = "季度报表";
if (ds.Tables.Count == 1)
saveFileDialog.FileName = "年度报表";
saveFileDialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
xlapp.Visible = true;
Workbooks workbooks = xlapp.Workbooks;
Workbook workbook = workbooks.Add(true);
if (ds.Tables.Count > 1)
{
workbook.Sheets.Add(miss,workbook.Sheets[1], ds.Tables.Count - 1, miss);
}
for (int d = 0; d < ds.Tables.Count; d++)
{
Worksheet ws = workbook.Worksheets[d + 1] as Worksheet;
if (ds.Tables.Count == 12)
ws.Name = d + 1 + "月份";
if (ds.Tables.Count == 4)
ws.Name = d + 1 + "季度";
if (ds.Tables.Count == 1)
ws.Name = "年度";
int totalCellsCount = ds.Tables[d].Columns.Count;
Range range = ws.Range[ws.Cells[1, 1], ws.Cells[1, ds.Tables[d].Columns.Count]];
//生成列
for (int i = 0; i < ds.Tables[d].Columns.Count; i++)
{
range.Cells[1, i + 1] = ds.Tables[d].Columns[i].ColumnName;
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.Font.Size = 10;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
}
//插入数据
for (int r = 0; r < ds.Tables[d].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[d].Columns.Count; i++)
{
ws.Cells[r + 2, i + 1] = "'" + ds.Tables[d].Rows[r][i].ToString();
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
}
((Worksheet)workbook.Sheets[1]).Select(true);//选中第一个worksheet
workbook.Close(true, miss, miss);//关闭workbook, 并保存对workbook的所有修改
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
xlapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
xlapp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
代码段三:
/// <summary>
/// 导出单Sheet 报表
/// </summary>
/// <param name="dt">数据</param>
public void DataToExcle(System.Data.DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("无数据!");
return;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xlsx";
saveFileDialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
saveFileDialog.RestoreDirectory = true;
saveFileDialog.Title = "导出数据到excel表格";
saveFileDialog.FileName = "报表";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
xlapp.Visible = true;
Workbooks workbooks = xlapp.Workbooks;
Workbook workbook = workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long totalcolumns = dt.Columns.Count;
//生成列
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//插入数据
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = "'" + dt.Rows[r][i].ToString();
}
}
((Worksheet)workbook.Sheets[1]).Select(true);//选中第一个worksheet
workbook.Close(true, miss, miss);//关闭workbook, 并保存对workbook的所有修改
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
xlapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
xlapp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
新手写代码,遇大佬指点,不胜感激,感谢观看