/// <summary>
/// 导出Excel方法：Excel导出模式，支持绑定下拉选择
/// </summary>
/// <param name="msg">执行返回的消息，非空时执行失败</param>
/// <param name="dt">导出的数据</param>
/// <param name="customColumnName">导出需要用到的自定义列名，如果为空，取数据源的表名</param>
/// <param name="ExportName">导出的文件名</param>
/// <param name="dicDropdown">哪一列需要绑定默认下拉值；字典Dictionary<int, string> 类型，int是需要绑定的列；string是需要绑定的下拉，多个值用逗号隔开</param>
/// <returns></returns>
public static bool OutputToExcel1(out string msg, System.Data.DataTable dt, string customColumnName = "", string ExportName = "", Dictionary<int, string> dicDropdown = null)
{
bool result = true;
msg = "";
try
{
System.Windows.Forms.SaveFileDialog dlg = new System.Windows.Forms.SaveFileDialog(); //保存文件对话框
dlg.Filter = "*.xls|*.xls"; //过滤文件类型
dlg.CreatePrompt = false; //提示创建文件
dlg.FileName = ExportName;
if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();//表示Excel应用程序
if (xlApp == null)
{
msg = "可能您的机子未安装excel，无法创建excel对象！";
return false;
}
Workbooks workbooks = xlApp.Workbooks; //workbook对象的集合
Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //表示一个workbook
Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //返回workbook的第一个worksheet
Microsoft.Office.Interop.Excel.Range allRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1 + dt.Columns.Count, 1 + dt.Rows.Count]];
allRange.NumberFormatLocal = "@";//文本格式
allRange.EntireColumn.AutoFit(); //全部列自适应宽度
allRange.EntireRow.AutoFit(); //全部行自适应高度
allRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//水平居中
allRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //垂直居中
//设置下拉选择
if (dicDropdown != null)
{
foreach (var item in dicDropdown)
{
//设定一定范围内单元格的格式
Microsoft.Office.Interop.Excel.Range myrange = worksheet.Columns[item.Key]; //worksheet.Range[worksheet.Cells[2, i], worksheet.Cells[2 + i, i]]; //第二行开始，到记录总数
//worksheet.Cells[1, 1 + i] = titleList[i];//为单元格赋值
myrange.Validation.Modify(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, item.Value, Type.Missing);
}
}
//设置标题
List<string> titleList = customColumnName.Split(',').ToList();
for (int i = 0; i < titleList.Count; i++)
{
//设定一定范围内单元格的格式
Microsoft.Office.Interop.Excel.Range myrange = worksheet.Range[worksheet.Cells[1, 1 + i], worksheet.Cells[1, 1 + i]];
worksheet.Cells[1, 1 + i] = titleList[i];//为单元格赋值
}
//插入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
//设定一定范围内单元格的格式
Microsoft.Office.Interop.Excel.Range myrange = worksheet.Range[worksheet.Cells[2 + i, 1 + j], worksheet.Cells[2 + i, 1 + j]];
worksheet.Cells[2 + i, 1 + j] = dt.Rows[i][j].ToString();//为单元格赋值
}
}
//保存及退出
workbook.Saved = true;
workbook.SaveCopyAs(dlg.FileName);
xlApp.Quit();
GC.Collect();//强行销毁
Process.Start(dlg.FileName); //打开EXCEL
}
}
catch (Exception ex)
{
msg = ex.Message;
result = false;
}
return result;
}
/// <summary>
/// 导出Excel方法：数据流方式，已文本方式导出
/// </summary>
/// <param name="dt">导出的数据</param>
/// <param name="customColumnName">导出需要用到的自定义列名，如果为空，取数据源的表名</param>
/// <param name="ExportName">导出的文件名</param>
/// <returns></returns>
public static bool OutputToExcel2(System.Data.DataTable dt, string customColumnName = "", string ExportName = "")
{
//if (dt == null || dt.Rows.Count == 0) return false; 支持导出空模版
bool flag = false;
System.Windows.Forms.SaveFileDialog dlg = new System.Windows.Forms.SaveFileDialog(); //保存文件对话框
dlg.Filter = "*.xls|*.xls"; //过滤文件类型
dlg.CreatePrompt = false; //提示创建文件
dlg.FileName = ExportName;
if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
//获取流对象
Stream myStream = dlg.OpenFile();
//获取流写入器对象
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string columnTitle = "";
try
{
//写入列标题
if (customColumnName != "")
{
columnTitle = customColumnName.Replace(",", "\t");
}
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i > 0)
{
columnTitle += "\t";
}
columnTitle += dt.Columns[i].ColumnName;
}
}
sw.WriteLine(columnTitle);
//写入列内容
for (int j = 0; j < dt.Rows.Count; j++)
{
string columnValue = "";
for (int k = 0; k < dt.Columns.Count; k++)
{
if (k > 0)
{
columnValue += "\t";
}
if (dt.Rows[j][k] == null)
columnValue += "";
else
columnValue += dt.Rows[j][k].ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
flag = true;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
sw.Close();
myStream.Close();
}
}
return flag;
}
网友评论