private void createExcel()
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;
try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "My New Sheet";
newSheet.Cells[1, 1] = "BOO!";
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
public bool CloseExcelFile()
{
try
{
if (xlsApp != null)
{
xlsWb.Save();
xlsApp.ActiveWorkbook.Save();
this.CloseExcelApplication();
}
return(true);
}
catch
{
this.CloseExcelApplication();
return(false);
}
}
/// <summary>
/// 保存Excel
/// </summary>
/// <returns>保存成功返回True</returns>
public bool Save()
{
if (myFileName == "")
{
return(false);
}
else
{
try
{
myWorkBook.Save();
return(true);
}
catch
{
return(false);
}
}
}
//保存文档
public bool Save()
{
if (mFilename == "")
{
return(false);
}
else
{
try
{
wb.Save();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
}
public static void AppendSheet(string sourceFile, string targetFile, string sheetName)
{
object MISSING = Type.Missing;
Excel.Application excel = new Excel.Application();
try
{
Excel.Workbook sourceBook = excel.Workbooks.Open(sourceFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
// Excel.Worksheet sourceSheet = (Excel.Worksheet)sourceBook.Sheets.Add(MISSING, sourceBook.Sheets[sourceBook.Sheets.Count], 1, MISSING);
Excel.Worksheet sourceSheet = (Excel.Worksheet)sourceBook.Sheets.Add(sourceBook.Sheets[1], MISSING, 1, MISSING);
sourceSheet.Name = sheetName;
sourceSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; //横打印xlLandscape 竖打印xlPortrait
sourceSheet.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
sourceSheet.PageSetup.Zoom = 75;
int row = 1;
using (StreamReader streamReader = new StreamReader(targetFile))
{
while (!streamReader.EndOfStream)
{
string s = streamReader.ReadLine();
if (!string.IsNullOrEmpty(s))
{
if (s.Substring(0, 1).Equals("="))
{
continue;
}
}
// 异常来自 HRESULT:0x800A03EC
Excel.Range sourceRange = (Excel.Range)sourceSheet.Cells[row, 1];
sourceRange.Value2 = s;
sourceRange.RowHeight = 12.75;
sourceRange.ColumnWidth = 150;
sourceRange.Font.Name = "宋体";
// sourceRange.Orientation = Excel.XlPageOrientation.xlPortrait;
row++;
}
streamReader.Close();
}
//Excel.Workbook targetBook = excel.Workbooks.Open(targetFile,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
//Excel.Worksheet targetSheet = (Excel.Worksheet)targetBook.Sheets[1];
// Excel 无法将工作表插入到目标工作簿中，因为目标工作簿的行数和列数比源工作簿少。
// 若要将数据移动到或复制到目标工作簿，可以选中数据，然后使用“复制”和“粘贴”命令将其插入其他工作簿的工作表中。
//targetSheet.Copy(MISSING, sourceSheet);
//targetBook.Close(false, MISSING, MISSING);
sourceBook.Save();
}
catch (Exception e)
{
string s = e.Message;
}
finally
{
Office.ExcelHelper.Kill(excel);
}
Thread.Sleep(100);
}
/// <summary>
/// 多张报表合成一个EXCEL文件
/// </summary>
/// <param name="tempDir">临时文件存放目录</param>
/// <param name="filename">指定导出的文件名，不指定的情况下，弹出对话框让用户选择</param>
/// <param name="sheetNames">工作表名列表</param>
/// <param name="url">报表服务器路径</param>
/// <param name="path">报表路径</param>
/// <param name="parms">参数列表</param>
/// <param name="isVisible">是否显示EXCEL，若不显示EXCEL，则自动保存到filename</param>
/// <remarks>
/// 最后清除临时文件
/// </remarks>
public static void ExportToOneExcelSave(string tempDir, string filename, string[] sheetNames, string url, string[] path,
ReportParameter[][] parms, bool isVisible)
{
if (filename == string.Empty)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "EXCEL(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
filename = sfd.FileName;
}
if (sfd.FileName == string.Empty)
{
return;
}
}
string[] excels = new string[path.Length];
string dt = DateTime.Now.ToString("yyyyMMddHHmmssfff");
for (int j = 0; j < excels.Length; j++)
{
excels[j] = string.Format(@"{0}\{1}{2}.xls", tempDir, dt, j);
}
for (int i = 0; i < path.Length; i++)
{
Export("EXCEL", excels[i], string.Empty, url, path[i],
(parms == null ? null : parms[i]));
}
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook1 = excel.Workbooks.Open(excels[0], Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Sheets[1];
worksheet1.Name = sheetNames[0];
worksheet1.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
for (int k = excels.Length - 1; k >= 1; k--)
{
Excel.Workbook workbook2 = excel.Workbooks.Open(excels[k], Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook2.Sheets[1];
worksheet2.Name = sheetNames[k];
worksheet2.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
worksheet2.Copy(Type.Missing, worksheet1);
workbook2.Close(false, Type.Missing, Type.Missing);
}
if (!isVisible)
{
workbook1.Save();
Office.ExcelHelper.Kill(excel);
if (File.Exists(tempDir + filename))
{
File.Delete(tempDir + filename);
}
Thread.Sleep(100);
File.Move(excels[0], tempDir + filename);
}
for (int m = 1; m < excels.Length; m++)
{
File.Delete(excels[m]);
}
//excel.Visible = isVisible;
}