public class ExcelUtil
{
internal static Excel.XlPaperSize GetXlPaperSize(PaperSize paperSize)
{
return (Excel.XlPaperSize)((int)paperSize);
}
public static Excel.Application CreateExcelApp(string sheetName)
{
Excel.Application xlsApp = new Excel.Application();
xlsApp.Application.Workbooks.Add(true);
if (!string.IsNullOrEmpty(sheetName))
{
xlsApp.Caption = sheetName;
xlsApp.Workbooks[1].Worksheets[1].Name = sheetName;
}
return xlsApp;
}
public static void PrintPreview(DataTable dt, int startRowIndex, int startColIndex, string sheetName, PaperSize paperSize, bool isLandscape, bool printGridlines){
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dt, startRowIndex, startColIndex);
if (!string.IsNullOrEmpty(sheetName))
{
sheetName = "&\"黑体\"&18 " + sheetName;
}
h.PrintSetUp(sheetName, GetXlPaperSize(paperSize),
isLandscape ? Excel.XlPageOrientation.xlLandscape : Excel.XlPageOrientation.xlPortrait, printGridlines);
PrintPreview(xlsApp, true);
}
public static void Print(DataTable dt, int startRowIndex, int startColIndex, string sheetName, PaperSize paperSize, bool isLandscape, bool printGridlines)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dt, startRowIndex, startColIndex);
if (!string.IsNullOrEmpty(sheetName))
{
sheetName = "&\"黑体\"&18 " + sheetName;
}
h.PrintSetUp(sheetName, GetXlPaperSize(paperSize),
isLandscape ? Excel.XlPageOrientation.xlLandscape : Excel.XlPageOrientation.xlPortrait, printGridlines);
Print(xlsApp, true);
}
public static void PrintPreview(DataGridView dgv, int startRowIndex, int startColIndex, string sheetName, PaperSize paperSize, bool isLandscape, bool printGridlines)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dgv, startRowIndex, startColIndex);
if (!string.IsNullOrEmpty(sheetName))
{
sheetName = "&\"黑体\"&18 " + sheetName;
}
h.PrintSetUp(sheetName, GetXlPaperSize(paperSize),
isLandscape ? Excel.XlPageOrientation.xlLandscape : Excel.XlPageOrientation.xlPortrait, printGridlines);
PrintPreview(xlsApp, true);
}
public static void Print(DataGridView dgv, int startRowIndex, int startColIndex, string sheetName, PaperSize paperSize, bool isLandscape, bool printGridlines)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dgv, startRowIndex, startColIndex);
if (!string.IsNullOrEmpty(sheetName))
{
sheetName = "&\"黑体\"&18 " + sheetName;
}
h.PrintSetUp(sheetName, GetXlPaperSize(paperSize),
isLandscape ? Excel.XlPageOrientation.xlLandscape : Excel.XlPageOrientation.xlPortrait, printGridlines);
Print(xlsApp, true);
}
public static void ExportToFile(DataTable dt, int startRowIndex, int startColIndex, string sheetName, string fileName)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dt, startRowIndex, startColIndex);
Save(xlsApp, fileName, true);
}
public static bool ExportToFile(DataTable dt, int startRowIndex, int startColIndex, string sheetName)
{
SaveFileDialog fg = new SaveFileDialog();
fg.Filter = "Excel 文件|*.xls|所有文件|*.*";
fg.FileName = sheetName + "_" + DateTime.Now.ToString("yyMMddHHmm") + ".xls";
if (fg.ShowDialog() == DialogResult.OK)
{
ExportToFile(dt, startRowIndex, startColIndex, sheetName, fg.FileName);
return true;
}
return false;
}
public static void ExportToFile(DataGridView dgv, int startRowIndex, int startColIndex, string sheetName, string fileName)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dgv, startRowIndex, startColIndex);
Save(xlsApp, fileName, true);
}
public static bool ExportToFile(DataGridView dgv, int startRowIndex, int startColIndex, string sheetName)
{
SaveFileDialog fg = new SaveFileDialog();
fg.Filter = "Excel 文件|*.xls|所有文件|*.*";
fg.FileName = sheetName + "_" + DateTime.Now.ToString("yyMMddHHmm") + ".xls";
if (fg.ShowDialog() == DialogResult.OK)
{
ExportToFile(dgv, startRowIndex, startColIndex, sheetName, fg.FileName);
return true;
}
return false;
}
public static void Export(DataTable dt, int startRowIndex, int startColIndex, string sheetName)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dt, startRowIndex, startColIndex);
xlsApp.Visible = true;
}
public static void Export(DataTable dt, int startRowIndex, int startColIndex)
{
Export(dt, startRowIndex, startColIndex, string.Empty);
}
public static void Export(DataTable dt)
{
Export(dt, 1, 1);
}
public static void Export(DataGridView dgv, int startRowIndex, int startColIndex, string sheetName)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = CreateExcelApp(sheetName);
ExcelEditHelper h = new ExcelEditHelper(xlsApp.Workbooks[1].Worksheets[1]);
h.Export(dgv, startRowIndex, startColIndex);
xlsApp.Visible = true;
}
public static void Export(DataGridView dgv, int startRowIndex, int startColIndex)
{
Export(dgv, startRowIndex, startColIndex, string.Empty);
}
public static void Export(DataGridView dgv)
{
Export(dgv, 1, 1);
}
public static void Import(string fileName, ref DataTable dt, int startRowIndex, int startColIndex)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
Excel.Application xlsApp = new Excel.Application();
Excel.Workbook xlBook = xlsApp.Workbooks.Open(fileName);
Excel.Worksheet xlSheet = xlBook.Application.Worksheets[1];
for (int i = startRowIndex + 1; i < xlSheet.Rows.Count; i++)
{
object objId = xlSheet.Cells[i, startColIndex].Value;
if (objId == null || string.IsNullOrEmpty(objId.ToString())) break;
dt.Rows.Add(dt.NewRow());
for (int j = 0; j < dt.Columns.Count; j++)
{
dt.Rows[i - startRowIndex - 1][j] = xlSheet.Cells[i, j + startColIndex].Value;
}
}
dt.AcceptChanges();
xlsApp.Workbooks.Close();
xlsApp.Quit();
KillExcelProcess(xlsApp);
}
public static bool Import(ref DataTable dt, int startRowIndex, int startColIndex)
{
OpenFileDialog fg = new OpenFileDialog();
fg.Filter = "Excel 文件|*.xls|所有文件|*.*";
fg.Multiselect = false;
if (fg.ShowDialog() == DialogResult.OK)
{
Import(fg.FileName, ref dt, startRowIndex, startColIndex);
return true;
}
return false;
}
public static bool Import(ref DataTable dt)
{
return Import(ref dt, 1, 1);
}
public static void PrintPreview(Excel.Application xlsApp, bool closeExcelAfterPreview)
{
xlsApp.Visible = true;
Excel.Worksheet xlsSheet = xlsApp.Workbooks[1].Worksheets[1];
xlsSheet.PrintPreview(true);
if (closeExcelAfterPreview)
{
xlsApp.Workbooks[1].Saved = true;
xlsApp.Workbooks.Close();
xlsApp.Quit();
KillExcelProcess(xlsApp);
}
}
public static void Print(Excel.Application xlsApp, bool closeExcelAfterPrint)
{
xlsApp.Visible = false;
xlsApp.ScreenUpdating = false;
Excel.Worksheet xlsSheet = xlsApp.Workbooks[1].Worksheets[1];
xlsSheet.PrintOut();
if (closeExcelAfterPrint)
{
xlsApp.Workbooks[1].Saved = true;
xlsApp.Workbooks.Close();
xlsApp.Quit();
KillExcelProcess(xlsApp);
}
}
public static void Save(Excel.Application xlsApp, string fileName, bool closeExcelAfterSave)
{
Excel.Worksheet xlsSheet = xlsApp.Workbooks[1].Worksheets[1];
xlsSheet.SaveAs(fileName);
if (closeExcelAfterSave)
{
xlsApp.Workbooks[1].Saved = true;
xlsApp.Workbooks.Close();
xlsApp.Quit();
KillExcelProcess(xlsApp);
}
}
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int pId);
public static void KillExcelProcess(Excel.Application xlsApp)
{
int pId;
GetWindowThreadProcessId(new IntPtr(xlsApp.Hwnd), out pId);
System.Diagnostics.Process.GetProcessById(pId).Kill();
}
}