ExcelUtil
来源:互联网 发布:2016全球互联网数据 编辑:程序博客网 时间:2024/05/10 17:27
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();
}
}
- ExcelUtil
- excelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil
- ExcelUtil.java
- ExcelUtil.java
- ExcelUtil(JXL)
- ExcelUtil excel工具类
- ExcelUtil-注解-SXSSFWorkbook
- Selenium学习16--ExcelUtil
- Excel工具类ExcelUtil
- 导入导出Excel工具类ExcelUtil
- 导入导出Excel工具类ExcelUtil
- 导入导出Excel工具类ExcelUtil
- java 导入导出Excel工具类ExcelUtil
- 5年后,你会是怎样的
- C++虚函数的原理
- CODE平台支持开源项目自动同步啦
- 2013网赛热身赛几道水题
- linux小记7 管道和重定向
- ExcelUtil
- Herding
- 请问各位大神一个关于 .net mf 编译的问题
- 判断一个数是否是素数
- 使用 python Matplotlib 库绘图
- 树形dp-hdu-4714-Tree2cycle
- hdu 1286 找新朋友
- 设计模式学习笔记总结
- 经典面试题:链表的相交与环问题