根据他人代码改写,原作者暂时找不到
1.每个sheet就是一张表,表名由DataSet的数据得到
2.不使用字符串数组来装数据,因为导出的数据在Excel里需要转换,直接用Excel单元格装数据。
protected void ExportExcel(DataSet ds)
{
if (ds == null) return;
KillProcess("Excel");
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel 2003 file|*.xls| Excel 2007 file|*.xlsx";
saveDialog.FileName = "Sheet1";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet tworksheet = null;
Microsoft.Office.Interop.Excel.Range range;
try
{
xlApp.DisplayAlerts = false;
for (int i = 0; i < ds.Tables.Count; i++)
{
tworksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
tworksheet.Name = ds.Tables[i].TableName.Trim();
int rowIndex = 1;
int colIndex = 0;
//导入字段
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
tworksheet.Cells[1, j + 1] = ds.Tables[i].Columns[j].ColumnName.Trim();
colIndex++;
}
string maxHeaderCell = NumberToChar(colIndex) + "1";
//tworksheet.get_Range("A1", maxHeaderCell).Font.Bold = true;
tworksheet.get_Range("A1", maxHeaderCell).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//导入sheet数据
for (int r = 0; r < ds.Tables[i].Rows.Count; r++)
{
for (int t = 0; t < ds.Tables[i].Columns.Count; t++)
{
tworksheet.Cells[r + 2, t + 1] = ds.Tables[i].Rows[r][ds.Tables[i].Columns[t].ColumnName.Trim()];
}
rowIndex++;
}
tworksheet.get_Range(tworksheet.Cells[1, 1], tworksheet.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
range = tworksheet.get_Range("A1", maxHeaderCell);
range.EntireColumn.AutoFit();
}
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[ds.Tables.Count + 1]).Delete();
string ext = Path.GetExtension(saveFileName);
//保存文件
if (ext == ".xls")
{
tworksheet.SaveAs(saveFileName, 56, missing, missing, missing, missing, missing, missing, missing);
}
else
{
tworksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing);
}
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, missing, missing);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
MessageBox.Show(saveFileName + "导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
xlApp.Quit();
}
private void KillProcess(string processName)
{
System.Diagnostics.Process myproc = new System.Diagnostics.Process();
//得到所有打开的进程
try
{
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
if (!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch (Exception Exc)
{
throw new Exception("", Exc);
}
}
private string NumberToChar(int number)
{
if (1 <= number && 26 >= number)
{
int num = number + 64;
System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
byte[] btNumber = new byte[] { (byte)num };
return asciiEncoding.GetString(btNumber);
}
else if (number > 26)
{
int NewNum = number % 26;
int count = number / 26;
string ss = NumberToChar(count);
int num = NewNum + 64;
System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
byte[] btNumber = new byte[] { (byte)num };
return ss + asciiEncoding.GetString(btNumber);
}
return "数字不在转换范围内";
}