public void ExportTeacherAllReports(string path, DateTime startDate, DateTime endDate)
{
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherDutyAll.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
ExportAllNormal(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportSingleStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
ExportSingle(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt);
m_objBook.SaveAs(path, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objFont = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <summary>
/// 将DataTable数据写入到Excel文件(若sheetName存在，则覆盖)
/// <para>若表为空则返回</para>
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="sheetName">Sheet名称</param>
public void WriteDataTableToExcelBook(DataTable dt, string sheetName)
{
if (obook == null)
{
throw new Exception("obook为null!");
}
if (dt == null)
{
return;
}
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.Worksheet oSheet = null;
try
{
//新建Sheet
foreach (Excel.Worksheet sht in obook.Sheets)
{
if (sht.Name == sheetName)
{
oSheet = sht;
}
}
if (oSheet == null)
{
oSheet = (Excel.Worksheet)obook.Sheets.Add(miss, miss, miss, miss);
oSheet.Name = sheetName;
}
int iRowCount = dt.Rows.Count;
int iColumnCount = dt.Columns.Count;
object[,] objVal = new object[iRowCount + 1, iColumnCount];
//将dataTable中数据写入
for (int i = 0; i <= iRowCount; i++)
{
for (int j = 0; j < iColumnCount; j++)
{
if (i == 0)
{
objVal[0, j] = dt.Columns[j].Caption;
}
else
{
objVal[i, j] = dt.Rows[i - 1][j].ToString();
}
}
}
//最后一列的字母表示
string Al = ExcelColumnNumb2Letter(iColumnCount);
Excel.Range xlRange = oSheet.get_Range("A1", Al + (iRowCount + 1).ToString());
xlRange.Value2 = objVal;
xlRange.EntireColumn.AutoFit();
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\n写入表单出错:" + sheetName);
}
}
/// <summary>
/// 导出到文件
/// </summary>
/// <param name="dataTable">数据源</param>
public static bool ToExcelFile(DataTable dataTable)
{
// 文件选择器保存文件位置
SaveFileDialog saveFileDialog = new SaveFileDialog
{
Filter = "Excel97-2003工作簿|*.xls"
};
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
int rowNum = dataTable.Rows.Count; //需要导出的数据的行数
int columnNum = dataTable.Columns.Count; //需要导出的数据的列数
int rowIndex = 1; //起始行为第二行
int columnIndex = 0; //起始列为第一列
Microsoft.Office.Interop.Excel.Range range; //Excel的格式设置
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application
{
DisplayAlerts = true,// 在程序执行过程中使出现的警告框显示
SheetsInNewWorkbook = 1
};
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
foreach (DataColumn dc in dataTable.Columns) //将datatable的列名导入excel表的第一行
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将数据写入到Excel表中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{//按行写入数据
columnIndex++;
range = (Microsoft.Office.Interop.Excel.Range)xlApp.Cells[rowIndex, columnIndex];
range.NumberFormatLocal = "@";//写入到表中的数据格式以文本形式存在
xlApp.Cells[rowIndex, columnIndex] = dataTable.Rows[i][j].ToString();
}
}
//数据保存
xlBook.SaveAs(saveFileDialog.FileName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
xlBook.Close(false, miss, miss);
xlApp.Quit();
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// DataSet導出到Excel，可以指定SheetName、文件名和是否要保存，也可以指定保存后是否自動退出
/// </summary>
/// <param name="ds">要導出的DataSet</param>
/// <param name="sheetNames">字符串數組，用來存放SheetName，數量需要和DataSet中的Table數一致，輸入null則自動給sheet編號</param>
/// <param name="fileName">指定的文件名，不帶路徑</param>
/// <param name="savePath">文件存放的路徑，最後不要帶\\</param>
public static void ExportToExcel(DataSet ds, string[] sheetNames, string fileName, string savePath)
{
if (ds == null)
{
return;
}
if (sheetNames == null)
{
sheetNames = new string[256];
}
System.Reflection.Missing Miss = System.Reflection.Missing.Value;
if (string.IsNullOrEmpty(fileName))
{
fileName = "Result.xlsx";
}
try
{
//创建一个工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
for (int i = 1; i <= ds.Tables.Count; i++)
{
//创建一个 sheet 表
string sheetName = (sheetNames[i - 1] != null) ? sheetNames[i - 1].ToString() : "Sheet" + i.ToString();
ISheet sheet = xssfworkbook.CreateSheet(sheetName);
WriteExcelModule(xssfworkbook, sheet, ds.Tables[i - 1]);
sheet = null;
}
if (savePath != "")
{
if (System.IO.Directory.Exists(savePath) == false)
{
System.IO.Directory.CreateDirectory(savePath);
}
}
else
{
savePath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
}
//创建文件
FileStream file = new FileStream(savePath + "\\" + fileName, FileMode.CreateNew, FileAccess.Write);
xssfworkbook.Write(file);
file.Close();
file.Dispose();
xssfworkbook.Close();
xssfworkbook = null;
}
catch (Exception ex)
{
}
}
/// <summary>
/// Exportar arquivo para .XLSx
/// </summary>
/// <param name="ds">Tabela com o relatório</param>
public bool ExportarXLS(System.Data.DataTable dt, string caminho)// Retirar do FrmConsulta e incluir na Classe
{
try
{
int inHeaderLength = 0, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
Application excelApp = new Application();
Workbook excelWorkBook = excelApp.Workbooks.Add(1);
//Create Excel WorkSheet
Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = "Relatório";//Name worksheet
//Write Column Name
for (int i = 0; i < dt.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dt.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dt.Rows.Count; m++)
{
for (int n = 0; n < dt.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = dt.Rows[m].ItemArray[n].ToString();
}
}
//Altera o formato da celula
excelWorkSheet.get_Range("D2").EntireColumn.NumberFormat = "DD/MM/YYYY";
//Delete First Page
excelApp.DisplayAlerts = false;
Worksheet lastWorkSheet = (Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
excelWorkBook.SaveAs(caminho, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkBook.Close();
excelApp.Quit();
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// 根据导入的Excel初始化字典表
/// </summary>
/// <param name="strExcelPath">Excel文件路径</param>
/// <param name="dicData"></param>
private void InitializeDic(string strExcelPath, ref Dictionary <string, object> dicData)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
if (xlsApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
try
{
Workbook xlsWrkBook = xlsApp.Workbooks.Open(strExcelPath, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
Worksheet xlsWrkSht = xlsWrkBook.Worksheets[1] as Worksheet;
for (int row = 1; row < xlsWrkSht.Rows.Count; row++)
{
///Excel中第二列为字段名
Range pRangeCoum = xlsWrkSht.get_Range(xlsWrkSht.Cells[row, 2], xlsWrkSht.Cells[row, 2]);
///第三列为字段值
Range pRangeRow = xlsWrkSht.get_Range(xlsWrkSht.Cells[row, 3], xlsWrkSht.Cells[row, 3]);
string strValue;
///当读到字段名为空时则默认为数据读取完成
if (pRangeCoum.Value2 == null)
{
break;
}
if (pRangeRow.Value2 == null)
{
//continue;
strValue = "";
}
else
{
strValue = pRangeRow.Value2.ToString();
}
dicData.Add(pRangeCoum.Value2.ToString(), strValue);
}
xlsApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWrkSht);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWrkBook);
}
catch { }
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
GC.Collect();
}
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelApp = null;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = null;
try
{
object target = targetPath;
object type = targetType;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
ExcelBook.ExportAsFixedFormat(targetType, target, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (ExcelBook != null)
{
ExcelBook.Close(true, missing, missing);
ExcelBook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
//private string projectPath = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
private bool CreatExcelTemolate()
{
bool result = false;
try
{
System.Reflection.Missing missing = System.Reflection.Missing.Value;
Excel.Application app = new Excel.ApplicationClass();
Excel.Workbooks workbooks = app.Workbooks;
Excel.Workbook workbook = workbooks.Add(missing);
Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet;
worksheet.Name = "Only This";
worksheet.Cells[1, 1] = "类型";
worksheet.Cells[1, 2] = "int";
worksheet.Cells[2, 1] = "描述";
worksheet.Cells[2, 2] = "对象的id";
worksheet.Cells[3, 1] = "字段名";
worksheet.Cells[3, 2] = "id";
string excelPathRoot = pathLable.Text + "/GameTable";
if (Directory.Exists(excelPathRoot) == false)
{
Directory.CreateDirectory(excelPathRoot);
}
worksheet.SaveAs(excelPathRoot + "/默认模板.xlsx");
workbook.Close();
app.Quit();
//打开文件夹
System.Diagnostics.Process.Start("explorer.exe", excelPathRoot + @"\");
}
catch (Exception e)
{
MessageBox.Show($"创建错误：{e.Message}");
}
return(result);
}
public void ExcelXLS(DirectoryInfo RutaExcel, DataTable Datos, string CadenaConexion)
{
ConexionBd conexion = new ConexionBd();
var NuevoArchivo = new FileInfo(RutaExcel + @"\xlExcel7Pass2.xls");
if (NuevoArchivo.Exists)
{
NuevoArchivo.Delete();
NuevoArchivo = new FileInfo(RutaExcel + @"\xlExcel7Pass2.xls");
}
Datos.TableName = "Polizas";
int inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
//Create Excel WorkSheet
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = "Poliza"; //Name worksheet
//Write Column Name
for (int i = 0; i < Datos.Columns.Count; i++)
{
excelWorkSheet.Cells[1, i + 1] = Datos.Columns[i].ColumnName; //.ToUpper();
}
//Write Rows
for (int m = 0; m < Datos.Rows.Count; m++)
{
for (int n = 0; n < Datos.Columns.Count; n++)
{
inColumn = n + 1;
inRow = 2 + m; //1 + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = Datos.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "W" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520");
}
}
}
////Excel Header
//OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "O1");
//cellRang.Merge(false);
//cellRang.Interior.Color = System.Drawing.Color.Blue;
//cellRang.Font.Color = System.Drawing.Color.Black;
//cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
//cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
//cellRang.Font.Size = 16;
//excelWorkSheet.Cells[1, 1] = "Greate Novels Of All Time";
//Style table column names
Excel.Range cellRang = excelWorkSheet.get_Range("A1", "W1");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#00008B");
cellRang = excelWorkSheet.get_Range("X1", "Z1");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520");
excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Formate price column
excelWorkSheet.get_Range("O2").EntireColumn.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"; //.NumberFormat = "0.00";
excelWorkSheet.get_Range("O2").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as Excel._Worksheet).Activate();
excelWorkBook.SaveAs(NuevoArchivo, Excel.XlFileFormat.xlExcel7, Default, Default, false, Default, Excel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
//excelWorkBook.SaveAs(NuevoArchivo, OfficeExcel.XlFileFormat.xlExcel5, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
//excelWorkBook.SaveAs(NuevoArchivo, OfficeExcel.XlFileFormat.xlExcel9795, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
}
private void ExportSingle(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0])
{
if (dt != null && dt.Rows.Count > 0)
{
DataView dv = dt.DefaultView;
using (DataTable dtBaseInfo = new TeacherBaseDataAccess().GetTcBaseInfo(string.Empty, string.Empty, string.Empty, string.Empty).Tables[0])
{
object[, ] objData = null;
foreach (DataRow dr in dtBaseInfo.Rows)
{
dv.RowFilter = "T_Number = " + dr[1].ToString();
objData = new object[dv.Count, 6];
if (dv.Count > 0)
{
for (int i = 0; i < dv.Count; i++)
{
objData[i, 0] = dv[i][3].ToString();
objData[i, 1] = dv[i][2].ToString();
objData[i, 2] = dv[i][0].ToString();
objData[i, 3] = dv[i][1].ToString();
objData[i, 4] = dv[i][4].ToString();
objData[i, 5] = dv[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Copy(Type.Missing, m_objSheet);
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(5));
m_objSheet.Name = objData[0, 0].ToString() + string.Format("({0})", objData[0, 1]);
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(dv.Count, 6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A" + (dv.Count + 4).ToString(), m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dv.Count + 4).ToString(), m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (dv.Count + 5).ToString(), m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dv.Count + 5).ToString(), m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (dv.Count + 6).ToString(), m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dv.Count + 6).ToString(), m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Delete();
}
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel_Click(object sender, EventArgs e)
{
RecordingBLL recordingBLL = new RecordingBLL();
DataTable dt = recordingBLL.GetAllUser(this.label1.Text);
//下载Nuget包 Microsoft.Office.Interop
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata;
Microsoft.Office.Interop.Excel.Range rangedata;
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookdata = appexcel.Workbooks.Add(miss);
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = "saved";
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
}
System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
xlrang = null;
//调用方法关闭excel进程
appexcel.Visible = true;
}
public void ExportToExcel(DataSet dataset, string FilePath, string number, string District, string ReportType = "")
{
int inHeaderLength = 2, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
//strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (DataTable dtbl in dataset.Tables)
{
//Create Excel WorkSheet
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName.ToUpper();//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count + 1; i++)
{
if (i == 0)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = "SL NO";
}
else
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i - 1].ColumnName.ToUpper();
}
}
//Write Rows
int ma = dtbl.Rows.Count;
int na = dtbl.Columns.Count + 2;
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count + 1; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
if (n == 0)
{
excelWorkSheet.Cells[inRow, inColumn] = m + 1;
}
else
{
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n - 1].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
na = n;
}
}
}
//Excel Header
Excel.Range cellRang = excelWorkSheet.get_Range("A1", ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "2");
cellRang.Merge(false);
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Gray;
cellRang.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 14;
excelWorkSheet.Cells[1, 1] = "Sales Report of " + DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd");
//excelWorkSheet.Cells[60, 5] = "=SUM(E4,E59)";
//excelWorkSheet.Cells[dtbl.Rows.Count + 2, dtbl.Columns.Count].Formula = a;
if (ReportType == "ZM")
{
//Create Total
int r = dtbl.Rows.Count + 3;
excelWorkSheet.Cells[dtbl.Rows.Count + 4, dtbl.Columns.Count + 1] = "=SUM(" + ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "4:" + ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "" + (dtbl.Rows.Count + 3) + ")";
//excelWorkSheet.Cells[dtbl.Rows.Count + 4, dtbl.Columns.Count + 1] = "=SUM(BN4:BN" + (dtbl.Rows.Count + 3) + ")";
((Excel.Range)excelWorkSheet.get_Range((dtbl.Rows.Count + 4) + ":" + dtbl.Columns.Count + 1)).Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
//Style table column names
cellRang = excelWorkSheet.get_Range("A3", ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "3");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.get_Range("E4").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Formate price column
//excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as Excel._Worksheet).Activate();
excelWorkBook.SaveAs(FilePath, Default, Default, Default, false, Default, Excel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
}
private void ExportSingleStat(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
object[,] objData;
using (DataTable dt = new DutyInfoDA().GetTeacherStatSingle(startDate, endDate))
{
if (dt != null && dt.Rows.Count > 0)
{
objData = new Object[dt.Rows.Count + 1, 6];
int days = SetAttendDays(startDate, endDate);
for (int i = 0; i < dt.Rows.Count; i++)
{
double attendCount = Convert.ToInt32(dt.Rows[i][2]);
double shouldAttendCount = days;
double absenceCount = shouldAttendCount - attendCount;
objData[i, 0] = dt.Rows[i][0].ToString();
objData[i, 1] = dt.Rows[i][1].ToString();
objData[i, 2] = shouldAttendCount;
objData[i, 3] = attendCount;
objData[i, 4] = absenceCount < 0 ? 0 : absenceCount;
objData[i, 5] = shouldAttendCount == 0 ? attendCount.ToString("0.00%") : (attendCount / shouldAttendCount).ToString("0.00%");
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3));
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, 6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 4).ToString(), m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 4).ToString(), m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 5).ToString(), m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 5).ToString(), m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 6).ToString(), m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 6).ToString(), m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
private void btn_GenExcel_Click(object sender, RoutedEventArgs e)
{
var dialogo = new winForm.FolderBrowserDialog();
if (dialogo.ShowDialog() != winForm.DialogResult.OK)
{
return;
}
string path = dialogo.SelectedPath;
string columna_final = "F";
DataSet ds = new DataSet();
DataTable table = new DataTable();
string tienda_nombre;
using (var db = new TiendaDbContext())
{
table.TableName = "Precios";
table.Columns.Add("Codigo");
table.Columns.Add("Descripcion");
table.Columns.Add("Cantidad Buen Estado");
table.Columns.Add("Precio Buen Estado");
table.Columns.Add("Cantidad Defectuoso");
table.Columns.Add("Precio Defectuoso");
var existencias = db.Tiendas.Find(tienda).Productos.Where(p => p.CantidadTotal > 0);
foreach (var item in existencias)
{
table.Rows.Add(item.Codigo, item.Producto.Descripcion, item.CantidadBuenEstado, item.PrecioBuenEstado, item.CantidadDefectuoso, item.PrecioDefectuoso);
}
tienda_nombre = existencias.First().Tienda.Nombre;
path += "\\" + tienda_nombre + " " + DateTime.Today.ToShortDateString().Replace('/', '-') + ".xlsx";
}
System.Reflection.Missing d = System.Reflection.Missing.Value;
int inHeaderLength = 3, inColumn = 0, inRow = 0;
OfficeExcel.Application excel_app = new OfficeExcel.Application();
OfficeExcel.Workbook excel_work_book = excel_app.Workbooks.Add(1);
OfficeExcel.Worksheet excel_sheet = excel_work_book.Sheets.Add(After: excel_work_book.Sheets[excel_work_book.Sheets.Count], Count: 1);
excel_sheet.Name = table.TableName;
for (int i = 0; i < table.Columns.Count; i++)
{
excel_sheet.Cells[inHeaderLength + 1, i + 1] = table.Columns[i].ColumnName.ToUpper();
}
for (int m = 0; m < table.Rows.Count; m++)
{
for (int n = 0; n < table.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excel_sheet.Cells[inRow, inColumn] = table.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excel_sheet.Range["A" + inRow.ToString(), columna_final + inRow.ToString()].Interior.Color = ColorTranslator.FromHtml("#FCE4D6");
}
}
}
excel_sheet.Range["D" + (inHeaderLength + 1).ToString()].EntireColumn.NumberFormat = "0.00";
excel_sheet.Range["F" + (inHeaderLength + 1).ToString()].EntireColumn.NumberFormat = "0.00";
var cell_header = excel_sheet.Range["A1", columna_final + inHeaderLength.ToString()];
cell_header.Merge(false);
cell_header.Interior.Color = System.Drawing.Color.White;
cell_header.Font.Color = System.Drawing.Color.Gray;
cell_header.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cell_header.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cell_header.Font.Size = 26;
excel_sheet.Cells[1, 1] = tienda_nombre;
cell_header = excel_sheet.Range["A" + (inHeaderLength + 1).ToString(), columna_final + (inHeaderLength + 1).ToString()];
cell_header.Font.Bold = true;
cell_header.Font.Color = System.Drawing.Color.White;
cell_header.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
// excel_sheet.Range(from, to)
// EntireColumn es para referirse a la columna
// NumberFormat = "0.00"
// NumberFormat = "MM/DD/YYYY" for dates
excel_sheet.Columns.AutoFit();
excel_app.DisplayAlerts = false;
OfficeExcel.Worksheet lastWS = (OfficeExcel.Worksheet)excel_work_book.Worksheets[1];
lastWS.Delete();
excel_app.DisplayAlerts = true;
(excel_work_book.Sheets[1] as OfficeExcel._Worksheet).Activate();
excel_work_book.SaveAs(Filename: path, ReadOnlyRecommended: false, AccessMode: OfficeExcel.XlSaveAsAccessMode.xlNoChange);
excel_work_book.Close();
MessageBox.Show("Se ha generado la tabla de precios");
}
private void ExportDataSetToExcel(DataSet ds, string strPath)
{
int inHeaderLength = 3, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
// strPath += DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
OfficeExcel.Application excelApp = new OfficeExcel.Application();
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName;//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "G" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
}
}
//Excel Header
OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "G3");
cellRang.Merge(false);
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Gray;
cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
excelWorkSheet.Cells[1, 1] = "Picquet Move Control System";
//Style table column names
cellRang = excelWorkSheet.get_Range("A4", "G4");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignRight;
//Formate price column
excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();
excelWorkBook.SaveAs(strPath, Default, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
MessageBox.Show("Excel generated successfully \n As " + strPath);
}
/// <summary>
/// Function to export dataset to excel
/// </summary>
/// <param name="ds"></param>
public static void ExportDataSetToExcel(DataSet ds, string strPath)
{
int inHeaderLength = 0, inColumn = 0, inRow = 0; //Declaration de int.
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
strPath += @"Bilan des appels HNO " + DateTime.Now.ToString("dd-MM-yyyy_hh-mm") + ".xlsx";
OfficeExcel.Application excelApp = new OfficeExcel.Application();
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
excelApp.Visible = true;
ds.Tables[0].Columns[0].ColumnName = "Date & Heure d'appel";
ds.Tables[0].Columns[1].ColumnName = "Numéro de ticket.";
ds.Tables[0].Columns[2].ColumnName = "Nom du Client";
ds.Tables[0].Columns[3].ColumnName = "Adresse du Client";
ds.Tables[0].Columns[4].ColumnName = "Nom de l'appelant";
ds.Tables[0].Columns[5].ColumnName = "Numéro de téléphone";
ds.Tables[0].Columns[6].ColumnName = "Description de la panne";
ds.Tables[0].Columns[7].ColumnName = "Technicien Appelé";
ds.Tables[0].Columns[8].ColumnName = "Observations";
foreach (System.Data.DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName = "Resumé";//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells.NumberFormat = "@"; // this allows us to keep the 0 at the start of our text elements!!!! important.
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "I" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
}
}
//Excel Header
Range line = (Range)excelWorkSheet.Rows[1];
line.Insert();
// Finding the last elements of my table :
OfficeExcel.Range last = excelWorkSheet.Cells.SpecialCells(OfficeExcel.XlCellType.xlCellTypeLastCell, Type.Missing);
OfficeExcel.Range range = excelWorkSheet.get_Range("A2", last);
//this draws around the range area between A2 and the last element of the table.
range.BorderAround(XlLineStyle.xlDouble);
// Excel table style
OfficeExcel.Range cellRang = excelWorkSheet.get_Range("B1", "H1");
cellRang.Merge(true); // makes a single cell between B1 and H1
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Blue;
cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
// this draws the borders of the excel document you selected.
cellRang.BorderAround(XlLineStyle.xlDouble);
//title of excel file
excelWorkSheet.Cells[1, 2] = "Title of EXCEL FILE";
//autofit errything together
excelWorkSheet.Columns.AutoFit();
////optional if needed
////[if you need it in an int]
//int lastUsedRow = last.Row;
//int lastUsedColumn = last.Column;
//// using last elements of the table.
//Style table column names
//cellRang = excelWorkSheet.get_Range("A4", "G4");
//cellRang.Font.Bold = true;
//cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
//cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
//excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignRight;
////Formate price column
//excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
////Auto fit columns
//excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete(); // Efface la feuille Feuil1 d'excel.
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();
excelWorkBook.SaveAs(strPath, Default, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default);
excelWorkBook.Close();
excelApp.Quit();
Console.WriteLine("Excel generated successfully \n As " + strPath);
return;
}
public void ExcelExport(System.Data.DataTable dt, string SheetName)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = appexcel.Workbooks.Add(System.Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets[1];
//Microsoft.Office.Interop.Excel.Range rangedata;
//创建Excel
//Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook ExcelBook = appexcel.Workbooks.Add(System.Type.Missing);
////创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
//Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
////设置Sheet标题
string start = "A1";
string end = ChangeASC(dt.Columns.Count) + "1";
Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)worksheetdata.get_Range(start, end);
_Range.Merge(0); //单元格合并动作(要配合上面的get_Range()进行设计)
_Range = worksheetdata.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_Range.Font.Size = 22; //设置字体大小
_Range.Font.Name = "宋体"; //设置字体的种类
worksheetdata.Cells[1, 1] = SheetName; //Excel单元格赋值
_Range.EntireColumn.AutoFit(); //自动调整列宽
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
//workbookdata = appexcel.Workbooks.Add(miss);
//worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = SheetName;
start = "A2";
end = ChangeASC(dt.Columns.Count) + "2";
_Range = worksheetdata.get_Range(start, end);
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[2, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
//rangedata = worksheetdata.get_Range("a3", miss);
//Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 1, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize + 1, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow <= irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow + 1;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
objval[i, j] = dt.Rows[i + iparstedrow - 1][j].ToString();
}
//System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
_Range = worksheetdata.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
_Range.Value2 = objval;
_Range.EntireColumn.AutoFit(); //自动调整列宽
_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(_Range);
_Range = null;
//调用方法关闭excel进程
appexcel.Visible = true;
}
//#region 导出全部数据到Excel中，可弹出保存对话框，但没用SaveFileDialog
//public void printAll(System.Data.DataTable dt)
//{
// //导出到execl
// try
// {
// //没有数据的话就不往下执行
// if (dt.Rows.Count == 0)
// return;
// //实例化一个Excel.Application对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
// excel.Application.Workbooks.Add(true);
// //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
// excel.Visible = false;
// //生成Excel中列头名称
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// excel.Cells[1, i + 1] = dgvList.Columns[i].HeaderText;//输出DataGridView列头名
// }
// //把DataGridView当前页的数据保存在Excel中
// if (dt.Rows.Count > 0)
// {
// for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完
// {
// for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完
// {
// string str = dt.Rows[i][j].ToString();
// excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制
// }
// }
// }
// //设置禁止弹出保存和覆盖的询问提示框
// excel.DisplayAlerts = false;
// excel.AlertBeforeOverwriting = false;
// //保存工作簿，值为false会报错
// excel.Application.Workbooks.Add(true).Save();
// //保存excel文件
// excel.Save("D:" + "\\KKHMD.xls");
// //确保Excel进程关闭
// excel.Quit();
// excel = null;
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "错误提示");
// }
//}
//#endregion
//#region 将DataGridView控件中数据导出到Excel
///// <summary>
///// 将DataGridView控件中数据导出到Excel
///// </summary>
///// <param name="gridView">DataGridView对象</param>
///// <param name="isShowExcle">是否显示Excel界面</param>
///// <returns></returns>
//public bool ExportDataGridview(DataGridView gridView, bool isShowExcle)
//{
// if (gridView.Rows.Count == 0)
// return false;
// //建立Excel对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// excel.Application.Workbooks.Add(true);
// excel.Visible = isShowExcle;
// //生成字段名称
// for (int i = 0; i < gridView.ColumnCount; i++)
// {
// excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
// }
// //填充数据
// for (int i = 0; i < gridView.RowCount - 1; i++)
// {
// for (int j = 0; j < gridView.ColumnCount; j++)
// {
// if (gridView[j, i].ValueType == typeof(string))
// {
// excel.Cells[i + 2, j + 1] = "'" + gridView[j, i].Value.ToString();
// }
// else
// {
// excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
// }
// }
// }
// return true;
//}
//#endregion
//#region 将所有数据导出到Excel：
//private void GenerateExcelAll(System.Data.DataTable dt, DataGridView dgv)
//{
// //导出到execl
// try
// {
// //没有数据的话就不往下执行
// if (dt.Rows.Count == 0)
// {
// MessageBox.Show("当前页面没有数据可以导出！", "导出结果", MessageBoxButtons.OK);
// return;
// }
// //实例化一个Excel.Application对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
// excel.Application.Workbooks.Add(true);
// //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
// excel.Visible = false;
// //生成Excel中列头名称
// //生成Excel中列头名称------------------------------**********************************---------------------------------------
// int columnCount = 0;
// for (int i = 0; i < dgv.Columns.Count; i++)
// {
// if (dgv.Columns[i].Visible)
// {
// columnCount = columnCount + 1;
// }
// }
// string[] headers = new string[columnCount];
// int index = 0;
// for (int i = 0; i < dgv.Columns.Count; i++)
// {
// if (dgv.Columns[i].Visible)
// {
// headers[index] = dgv.Columns[i].HeaderText;
// index = index + 1;
// }
// }
// for (int i = 0; i < columnCount; i++)
// {
// excel.Cells[1, i + 1] = headers[i];
// //if (dgv.Columns[i].ValueType == typeof(DateTime))
// //{
// // Microsoft.Office.Interop.Excel.Range headRange = excel.Cells[1, i - 1] as Microsoft.Office.Interop.Excel.Range;// as Range;//获取表头单元格
// // headRange.ColumnWidth = 22;//设置列宽
// //}
// }
// //---------------------------------------------------**********************************----------------------------------------
// ////////----------------------------------------**************************************---------------------------------------------
// //把DataGridView当前页的数据保存在Excel中
// if (dt.Rows.Count > 0)
// {
// int ccc = dgv.Columns.Count;
// for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完
// {
// int columnIndex = 0;
// for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完
// {
// if (dgv.Columns[j].Visible)
// {
// columnIndex = columnIndex + 1;
// string str = dt.Rows[i][j].ToString();
// excel.Cells[i + 2, columnIndex] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制
// }
// }
// }
// }
// //设置禁止弹出保存和覆盖的询问提示框
// excel.DisplayAlerts = false;
// excel.AlertBeforeOverwriting = false;
// //保存工作簿，值为false会报错
// //excel.Application.Workbooks.Add(false);//.Save();
// //确保Excel进程关闭
// excel.Save();
// excel.Quit();
// excel = null;
// MessageBox.Show("您所查询的所有数据已经成功导出到您指定的目录！", "导出结果", MessageBoxButtons.OK);
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "错误提示");
// }
//}
//#endregion
#endregion
/// <summary>
/// 另存新档按钮 导出成Excel
/// </summary>
private void SaveToExcel() //另存新档按钮 导出成Excel
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File To";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (string.IsNullOrEmpty(strName))
{
return;
}
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
int colIndex = 0;
foreach (DataGridViewColumn column in dgvList.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = column.HeaderText;
}
for (int i = 0; i < dgvList.Rows.Count; i++)
{
for (int j = 0; j < dgvList.Columns.Count; j++)
{
if (dgvList.Columns[j].Name == "IdCard")
{
excel.Cells[i + 2, j + 1] = "'" + dgvList.Rows[i].Cells[j].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvList.Rows[i].Cells[j].Value.ToString();
}
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
}
string ExportDataSetToExcel(DataSet ds, string strPath, string AppointmentDate, string ContractorName)
{
try
{
string pathImg = strPath + "Logo.png";
int inHeaderLength = 9, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
strPath += ContractorName + "_" + AppointmentDate + ".xlsx";
if (File.Exists(strPath))
{
File.Delete(strPath);
}
OfficeExcel.Application excelApp = new OfficeExcel.Application();
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = (OfficeExcel.Worksheet)excelWorkBook.Sheets["Sheet1"];
excelWorkSheet.Name = dtbl.TableName;
//Insert logo company
var filePath = pathImg;
if (!File.Exists(filePath))
{
Resource1.Logo.Save(filePath);
}
excelWorkSheet.Shapes.AddPicture(filePath, MsoTriState.msoFalse, MsoTriState.msoCTrue, 0, 0, 200, 45);
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 1 + m;
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
}
}
int EndRow = dtbl.Rows.Count + inHeaderLength;
excelWorkSheet.get_Range("A10", "G" + EndRow).HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignLeft;
excelWorkSheet.get_Range("A1", "G" + EndRow).Cells.Font.Name = "Times New Roman";
OfficeExcel.Range cellrange = excelWorkSheet.get_Range("A9", "G" + EndRow);
cellrange.Borders.LineStyle = OfficeExcel.XlLineStyle.xlContinuous;
cellrange.Borders.Weight = OfficeExcel.XlBorderWeight.xlThin;
cellrange.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
//Excel Header
OfficeExcel.Range cellHeaderVN = excelWorkSheet.get_Range("A1", "G3");
cellHeaderVN.Merge(false);
cellHeaderVN.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellHeaderVN.Font.Bold = OfficeExcel.XlBorderWeight.xlMedium;
cellHeaderVN.Font.Size = 18;
excelWorkSheet.Cells[1, 1] = "Phiếu đóng tiền làm thẻ cho công nhân nhà thầu ";
OfficeExcel.Range cellHeaderTW = excelWorkSheet.get_Range("A4", "G4");
cellHeaderTW.Merge(false);
cellHeaderTW.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellHeaderTW.Font.Bold = OfficeExcel.XlBorderWeight.xlMedium;
cellHeaderTW.Font.Size = 18;
excelWorkSheet.Cells[4, 1] = "承包商工人建卡付款表";
//Contractor Name
OfficeExcel.Range cellContractorName = excelWorkSheet.get_Range("A6", "G6");
cellContractorName.Merge(false);
cellContractorName.Font.Bold = OfficeExcel.XlBorderWeight.xlMedium;
excelWorkSheet.Cells[6, 1] = "Tên nhà thầu 承包商名稱: " + ContractorName;
// export report date
OfficeExcel.Range cellHeaderAppDate = excelWorkSheet.get_Range("A7", "G7");
cellHeaderAppDate.Merge(false);
excelWorkSheet.Cells[7, 1] = "Ngày hẹn đăng ký thẻ 預計登記卡日期: " + AppointmentDate;
//Style table column names
OfficeExcel.Range cellHeader = excelWorkSheet.get_Range("A9", "G9");
cellHeader.Font.Bold = true;
cellHeader.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#C5D9F1");
cellHeader.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
//Total Price
OfficeExcel.Range cellTotalPrice = excelWorkSheet.get_Range("E" + EndRow, "F" + EndRow);
cellTotalPrice.Font.Color = System.Drawing.Color.Red;
cellTotalPrice.Font.Bold = true;
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
excelWorkSheet.get_Range("G:G", System.Type.Missing).EntireColumn.ColumnWidth = 27;
}
//Set Defualt Page
(excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();
excelWorkBook.SaveAs(strPath);
excelWorkBook.Close();
excelApp.Quit();
return(strPath);
}
catch (Exception e)
{
Loger.Error(e);
throw new Exception(e.Message);
}
}
public List <cls_Sheet0home_info> ReadfindngFile(string instertext)
{
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
try
{
List <cls_Sheet0home_info> Result = new List <cls_Sheet0home_info>();
List <cls_Sheet0home_info> filterResult = new List <cls_Sheet0home_info>();
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(instertext, Type.Missing, true, Type.Missing,
"htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets[2];
Microsoft.Office.Interop.Excel.Range rng;
rng = WS.get_Range(WS.Cells[2, 1], WS.Cells[WS.UsedRange.Rows.Count, 30]);
int rowCount = WS.UsedRange.Rows.Count - 1;
object[,] o = new object[1, 1];
o = (object[, ])rng.Value2;
for (int i = 1; i <= rowCount; i++)
{
bgWorker.ReportProgress(0, "读入数据中 筛选库 : " + i.ToString() + "/" + rowCount.ToString());
cls_Sheet0home_info temp = new cls_Sheet0home_info();
#region 基础信息
temp.guanjianci_A = "";
if (o[i, 1] != null)
{
temp.guanjianci_A = o[i, 1].ToString().Trim();
}
if (temp.guanjianci_A == "" || temp.guanjianci_A == null)
{
continue;
}
temp.beizhu1 = "";
if (o[i, 1] != null)
{
temp.beizhu1 = "行-" + i.ToString().Trim();
}
#endregion
filterResult.Add(temp);
}
WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets[1];
rng = WS.get_Range(WS.Cells[2, 1], WS.Cells[WS.UsedRange.Rows.Count, 30]);
rowCount = WS.UsedRange.Rows.Count - 1;
o = new object[1, 1];
o = (object[, ])rng.Value2;
for (int i = 1; i <= rowCount; i++)
{
bgWorker.ReportProgress(0, "读入数据中1 : " + i.ToString() + "/" + rowCount.ToString());
cls_Sheet0home_info temp = new cls_Sheet0home_info();
#region 基础信息
temp.guanjianci_A = "";
if (o[i, 1] != null)
{
temp.guanjianci_A = o[i, 1].ToString().Trim();
}
if (temp.guanjianci_A == "" || temp.guanjianci_A == null)
{
continue;
}
temp.sousuorenqi_B = "";
if (o[i, 2] != null)
{
temp.sousuorenqi_B = o[i, 2].ToString().Trim();
}
temp.zaixianshangpinshu_C = "";
if (o[i, 3] != null)
{
temp.zaixianshangpinshu_C = o[i, 3].ToString().Trim();
}
temp.zhifuzhuanhuanlv_D = "";
if (o[i, 4] != null)
{
temp.zhifuzhuanhuanlv_D = o[i, 4].ToString().Trim();
}
temp.dianjilv_E = "";
if (o[i, 5] != null)
{
temp.dianjilv_E = o[i, 5].ToString().Trim(); //clsCommHelp.objToDateTime(o[i, 5]);
}
temp.shangchengdianjizhanbi_F = "";
if (o[i, 6] != null)
{
temp.shangchengdianjizhanbi_F = o[i, 6].ToString().Trim(); //clsCommHelp.objToDateTime(o[i, 6]);
}
temp.sousuoci_G = "";
if (o[i, 7] != null)
{
temp.sousuoci_G = o[i, 7].ToString().Trim();
}
temp.anlanqi_H = "";
if (o[i, 8] != null)
{
temp.anlanqi_H = o[i, 8].ToString().Trim();
}
#endregion
if (temp.guanjianci_A == "viv0官方旗舰店手机")
{
}
List <cls_Sheet0home_info> filtered = filterResult.FindAll(s => temp.guanjianci_A.Contains(s.guanjianci_A));
if (filtered.Count > 0)
{
temp.beizhu1 = "无效";
WS.Cells[i + 1, 12] = temp.beizhu1;
}
Result.Add(temp);
}
if (this.radioButton2.Checked == true)
{
//excelApp.Visible = true;
//excelApp.ScreenUpdating = true;
List <cls_Sheet0home_info> DeleteList = Result.FindAll(s => s.beizhu1 != null && s.beizhu1.Contains("无效"));
string[] s11 = new string[DeleteList.Count];
for (int i = 0; i < DeleteList.Count; i++)
{
s11[i] = DeleteList[i].beizhu1;
}
WS.get_Range(WS.Cells[1, 1], WS.Cells[1, 10]).AutoFilter(9, s11, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);
WS.get_Range(WS.Cells[2, 1], WS.Cells[50000, 20]).EntireRow.Delete(0);
WS.get_Range(WS.Cells[1, 1], WS.Cells[1, 10]).AutoFilter(9, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, false);
}
//excelApp.Visible = true;
//excelApp.ScreenUpdating = true;
analyWK.SaveAs(strFileName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue);
excelApp.DisplayAlerts = false;
clsCommHelp.CloseExcel(excelApp, analyWK);
return(Result);
}
catch (Exception ex)
{
MessageBox.Show("表格存在异常,请参照原始表格格式修改:" + ex.Message);
return(null);
throw ex;
}
}
/// <summary>
/// 导出速度最快
/// </summary>
/// <param name="list"><列名，数据></param>
/// <param name="filepath"></param>
/// <returns></returns>
public static void NewExport(List <DictionaryEntry> list, string filepath)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = null;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata = null;
Microsoft.Office.Interop.Excel.Range rangedata;
workbookdata = appexcel.Workbooks.Add();
//设置对象不可见
appexcel.Visible = false;
appexcel.DisplayAlerts = false;
try
{
foreach (var lv in list)
{
var keys = lv.Key as List <string>;
var values = lv.Value as List <IList <object> >;
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, workbookdata.ActiveSheet);
for (int i = 0; i < keys.Count - 1; i++)
{
//给工作表赋名称
worksheetdata.Name = keys[0];//列名的第一个数据位表名
worksheetdata.Cells[1, i + 1] = keys[i + 1];
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a1", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = values.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 10000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = keys.Count - 1;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
var v = values[i + iparstedrow][j];
objval[i, j] = v != null?v.ToString() : "";
}
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
xlrang.NumberFormat = "@";
// 调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
}
((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet1"]).Delete();
((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet2"]).Delete();
((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet3"]).Delete();
//保存工作表
workbookdata.SaveAs(filepath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbookdata.Close(false, miss, miss);
appexcel.Workbooks.Close();
appexcel.Quit();
appexcel = null;
}
catch (Exception)
{
throw;
}
finally
{
if (appexcel != null)
{
workbookdata.Close(true, Type.Missing, Type.Missing);
workbookdata = null;
appexcel.Quit();
appexcel = null;
}
}
}
public void ImportCardExcelFile(string id, string name, string grade,
string atClass, bool isStu, string savePath)
{
if (grade.Equals("全部"))
{
grade = string.Empty;
}
if (atClass.Equals("全部"))
{
atClass = string.Empty;
}
DataSet cardInfo;
object[,] objData;
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
if (isStu)
{
using (CardInfoDA cardInfoDA = new CardInfoDA())
{
cardInfo = cardInfoDA.GetStuCardNumberForExcel(id, name, grade, atClass);
}
if (cardInfo.Tables[0].Rows.Count > 0)
{
objData = new Object[cardInfo.Tables[0].Rows.Count, 7];
for (int i = 0; i < cardInfo.Tables[0].Rows.Count; i++)
{
objData[i, 0] = cardInfo.Tables[0].Rows[i][0].ToString();
objData[i, 1] = cardInfo.Tables[0].Rows[i][1].ToString();
objData[i, 2] = cardInfo.Tables[0].Rows[i][2].ToString();
objData[i, 3] = cardInfo.Tables[0].Rows[i][3].ToString();
objData[i, 4] = cardInfo.Tables[0].Rows[i][4].ToString();
objData[i, 5] = cardInfo.Tables[0].Rows[i][5].ToString();
objData[i, 6] = cardInfo.Tables[0].Rows[i][6].ToString();
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\StudentCardInfo.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(cardInfo.Tables[0].Rows.Count, 7);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
else
{
using (CardInfoDA cardInfoDA = new CardInfoDA())
{
cardInfo = cardInfoDA.GetTeaCardNumberForExcel(id, name, grade, atClass);
}
if (cardInfo.Tables[0].Rows.Count > 0)
{
objData = new Object[cardInfo.Tables[0].Rows.Count, 6];
for (int i = 0; i < cardInfo.Tables[0].Rows.Count; i++)
{
objData[i, 0] = cardInfo.Tables[0].Rows[i][0].ToString();
objData[i, 1] = cardInfo.Tables[0].Rows[i][1].ToString();
objData[i, 2] = cardInfo.Tables[0].Rows[i][2].ToString();
objData[i, 3] = cardInfo.Tables[0].Rows[i][3].ToString();
objData[i, 4] = cardInfo.Tables[0].Rows[i][4].ToString();
objData[i, 5] = cardInfo.Tables[0].Rows[i][5].ToString();
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherCardInfo.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(cardInfo.Tables[0].Rows.Count, 6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objFont = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
private static void ToExcel(List <Sheet> sheets, List <DataTable> dts, string file)
{
excel.Application appexcel = null;
excel.Workbook workbookdata = null;
excel.Worksheet worksheetdata = null;
try
{
appexcel = new excel.Application();
workbookdata = appexcel.Workbooks.Add();
//设置对象不可见
appexcel.Visible = false;
appexcel.DisplayAlerts = false;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
int sheetNum = 0;
foreach (var sheet in sheets)
{
DataTable dt = dts[sheetNum];
worksheetdata = (excel.Worksheet)workbookdata.Worksheets.Add(miss, workbookdata.ActiveSheet);
//给工作表赋名称
worksheetdata.Name = sheet.SheetName;
for (int i = 0; i < sheet.Fileds.Count; i++)
{
worksheetdata.Cells[1, i + 1] = sheet.Fileds[i].RowName;
}
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
//已执行的行数
int iparstedrow = 0;
//单次执行的条数
int icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 10000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = sheet.Fileds.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
{
icurrsize = irowcount - iparstedrow;
}
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
DataRow row = dt.Rows[iparstedrow + i];
for (int j = 0; j < icolumnaccount; j++)
{
if (dt.Columns.Contains(sheet.Fileds[j].FldName))
{
var v = row[sheet.Fileds[j].FldName];
objval[i, j] = v != null?v.ToString() : "";
}
else
{
objval[i, j] = "N/A";
}
}
}
string X = "A" + ((int)(iparstedrow + 2)).ToString(); //因为第一行已经写了表头，所以所有数据都应该从a2开始
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
excel.Range xlrang = worksheetdata.get_Range(X, col);
xlrang.NumberFormat = "@";
//调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
sheetNum++;
}
((excel.Worksheet)workbookdata.Worksheets["Sheet1"]).Delete();
((excel.Worksheet)workbookdata.Worksheets["Sheet2"]).Delete();
((excel.Worksheet)workbookdata.Worksheets["Sheet3"]).Delete();
//保存工作表
workbookdata.SaveAs(file, miss, miss, miss, miss, miss, excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbookdata.Close(false, miss, miss);
appexcel.Workbooks.Close();
appexcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookdata);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel);
GC.Collect();
}
catch (Exception ex)
{
throw;
}
finally
{
}
}
public void ImportTeaOutDetailsReports(DataSet teaOutInfo, string savePath, DateTime getBegDate, DateTime getEndDate)
{
object[,] objData;
string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value;
try
{
if (teaOutInfo.Tables[0].Rows.Count > 0)
{
objData = new Object[teaOutInfo.Tables[0].Rows.Count, 8];
for (int i = 0; i < teaOutInfo.Tables[0].Rows.Count; i++)
{
objData[i, 0] = teaOutInfo.Tables[0].Rows[i][0].ToString();
objData[i, 1] = teaOutInfo.Tables[0].Rows[i][1].ToString();
objData[i, 2] = teaOutInfo.Tables[0].Rows[i][2].ToString();
objData[i, 3] = teaOutInfo.Tables[0].Rows[i][3].ToString();
objData[i, 4] = teaOutInfo.Tables[0].Rows[i][4].ToString();
objData[i, 5] = teaOutInfo.Tables[0].Rows[i][5].ToString();
objData[i, 6] = teaOutInfo.Tables[0].Rows[i][6].ToString();
objData[i, 7] = teaOutInfo.Tables[0].Rows[i][7].ToString();
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherOutDetails.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(teaOutInfo.Tables[0].Rows.Count, 8);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A" + (teaOutInfo.Tables[0].Rows.Count + 4).ToString(), m_objOpt);
m_objRange.Value = "园所: " + new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (teaOutInfo.Tables[0].Rows.Count + 5).ToString(), m_objOpt);
m_objRange.Value = "统计日期: " + getBegDate.ToString("yyyy.MM.dd") + "-" + getEndDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 12;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objFont = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
protected void Excel_Click(object sender, ImageClickEventArgs e)
{
List <Data> data = GetData();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
//создаем и инициализируем объекты Excel
Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
//добавляем в файл Excel книгу. Параметр в данной функции - используемый для создания книги шаблон.
//если нас устраивает вид по умолчанию, то можно спокойно передавать пустой параметр.
Excel.Workbooks xlsWBs = App.Workbooks;
Excel.Workbook xlsWB = xlsWBs.Add(missingValue);
//и использует из нее
Excel.Sheets xlsSheets = xlsWB.Worksheets;
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsSheets.get_Item(1);
string[] cols = { "№ события", "Источник", "Диспетчер", "дата/время", "Вид услуги", "Зона", "Событие", "Описание", "Принял", "дата/время",
"Исполнил", "Комментарий", "дата/время", "Тайминг" };
for (int i = 0; i < cols.Length; i++)
{
xlsSheet.Cells[1, i + 1] = cols[i];
}
for (int i = 0; i < data.Count; i++)
{
xlsSheet.Cells[i + 2, 1] = data[i].Id;
xlsSheet.Cells[i + 2, 2] = data[i].Sourse;
xlsSheet.Cells[i + 2, 3] = data[i].IO;
xlsSheet.Cells[i + 2, 4] = data[i].Date1;
xlsSheet.Cells[i + 2, 5] = data[i].RegistrId;
xlsSheet.Cells[i + 2, 6] = data[i].LiftId;
xlsSheet.Cells[i + 2, 7] = data[i].TypeId;
xlsSheet.Cells[i + 2, 8] = data[i].EventId;
xlsSheet.Cells[i + 2, 9] = data[i].ToApp;
xlsSheet.Cells[i + 2, 10] = data[i].DateToApp;
xlsSheet.Cells[i + 2, 11] = data[i].Who;
xlsSheet.Cells[i + 2, 12] = data[i].Comment;
xlsSheet.Cells[i + 2, 13] = data[i].Date2;
xlsSheet.Cells[i + 2, 14] = data[i].Timing;
}
string name = DateTime.Now.ToString("ddMMyyyy-hhmm") + User.Identity.Name + ".xls";
xlsWB.SaveAs(Request.PhysicalApplicationPath + KOS.App_Code.ClearTemp._folder + "\\" + name,
Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
missingValue,
missingValue,
missingValue,
missingValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue);
//закрываем книгу
xlsWB.Close(false, missingValue, missingValue);
xlsWB = null;
xlsWBs = null;
xlsSheet = null;
xlsSheets = null;
//закрываем приложение
App.Quit();
//уменьшаем счетчики ссылок на COM объекты, что, по идее должно их освободить.
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheets);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWBs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
Download.NavigateUrl = "~/" + KOS.App_Code.ClearTemp._folder + "\\" + name;
Download.Text = "Скачать документ";
Download.Visible = true;
}
/// <summary>
/// json 导出到excel
/// </summary>
/// <param name="json">传入json数组（单级）</param>
/// <param name="filepath">导出路径</param>
/// <param name="title">到处数据表的列头名</param>
/// <returns></returns>
public bool Json2Excel(string json, string filepath, string title = "")
{
//json转array
JavaScriptSerializer js = new JavaScriptSerializer();
ArrayList lists = js.Deserialize <ArrayList>(json);
//导出状态
bool bSuccess = true;
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = null;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata = null;
Microsoft.Office.Interop.Excel.Range rangedata;
workbookdata = appexcel.Workbooks.Add();
//设置对象不可见
appexcel.Visible = false;
appexcel.DisplayAlerts = false;
try
{
//新建工作簿
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, workbookdata.ActiveSheet);
Dictionary <string, object> item_0 = (Dictionary <string, object>)lists[0];
int keys_count = item_0.Keys.Count, items_count = lists.Count;
int xx = 0;
//如果有手工赋值的列表名字，那就用手工赋值的，如果没有，就用字段名
if (title != "")
{
string[] titles = title.Split(new char[1] {
'|'
});
for (int TI = 0; TI < titles.Length; TI++)
{
worksheetdata.Cells[1, TI + 1] = titles[TI];
xx++;
}
}
else
{
foreach (string key in item_0.Keys)
{
if (key.Substring(key.IndexOf("_"), key.Length) != "TEMP" && key.Substring(key.IndexOf("_"), key.Length) != "BTN")
{
//为列头赋值
worksheetdata.Cells[1, xx + 1] = key;
xx++;
}
}
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = items_count; //实际行数？？
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
//int ieachsize = 10000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = keys_count;
//确定表格的写入范围
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(items_count + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
//x：左上角的单元格 col：右下角的单元格或整行
xlrang = worksheetdata.get_Range(X, col);
xlrang.NumberFormat = "@";
object[,] insert_infos = new object[items_count, keys_count];
int row_count = 0;
foreach (Dictionary <string, object> item in lists)
{
int col_count = 0;
foreach (string key in item.Keys)
{
if (key.IndexOf("_") >= 0)
{
switch (key.Substring(key.IndexOf("_") + 1, key.Length - key.IndexOf("_") - 1))
{
case "TEMP":
break;
case "DATE":
insert_infos[row_count, col_count] = item[key].ToString().Substring(0, 10);
col_count++;
break;
case "COMMAS":
insert_infos[row_count, col_count] = String.Format("{0:N2}", item[key]);
col_count++;
break;
case "PERCENT":
insert_infos[row_count, col_count] = (Convert.ToDouble(item[key]) * 100).ToString() + "%";
col_count++;
break;
case "BTN":
break;
default:
insert_infos[row_count, col_count] = item[key];
col_count++;
break;
}
}
else
{
insert_infos[row_count, col_count] = item[key];
col_count++;
}
}
row_count++;
}
// 调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = insert_infos;
//((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet1"]).Delete();
//((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet2"]).Delete();
//((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet3"]).Delete();
//保存工作表
workbookdata.SaveAs(filepath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbookdata.Close(false, miss, miss);
appexcel.Workbooks.Close();
appexcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookdata);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel);
GC.Collect();
}
catch (Exception ex)
{
string ErrorMsg = ex.Message;
bSuccess = false;
}
finally
{
if (appexcel != null)
{
// ExcelImportHelper.KillSpecialExcel(appexcel);
}
}
return(bSuccess);
}
/// <summary> 导出当前页DataGridView中的数据到EXcel中
/// 导出当前页DataGridView中的数据到EXcel中
/// </summary>
/// <param name="strName">strName</param>
public bool ExportTOExcel2(string strName, ref string sMsg)
{
if (ListLayer.Count == 0)
{
sMsg = "没有数据可供导出！";
return(false);
}
if (strName.Length == 0)
{
sMsg = "文件路径[" + strName + "]不对！";
return(false);
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
try
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
excel.Visible = false; //若是true，则在导出的时候会显示EXcel界面。
excel.AlertBeforeOverwriting = false; //关闭修改之后是否保存
if (excel == null)
{
sMsg = "EXCEL无法启动！";
return(false);
}
Workbook book;
if (File.Exists(strName))
{
book = excel.Workbooks.Open(strName, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);//打开Excel
}
else
{
book = excel.Workbooks.Add(miss); //打开Excel
}
Worksheet sheet = book.Sheets[1] as Worksheet; //得到Excel的第一个sheet
sheet.Name = "Sheet1";
//生成Excel中列头名称
excel.Cells[1, 1] = "管理层";
for (int i = 0; i < ListLayer.Count; i++)
{
excel.Cells[1, i + 2] = (i + 1).ToString();
}
//把DataGridView当前页的数据保存在Excel中
for (int i = 0; i < ListLayer[0].ListCell.Count; i++)
{
//string str1 = CStrPublicFun.Get2StrTo1(ListLayer[0].ListCell[i].Name, ListLayer[0].ListCell[i].Linker, 40, ' ');
//excel.Cells[i + 2, 1] = str1.Trim();
for (int j = 0; j < ListLayer.Count; j++)
{
excel.Cells[i + 2, j + 2] = ListLayer[j].ListCell[i].StrValue;
}
}
/* for (int i = 0; i < ListLayer[0].ListIndex.Count - 1; i++)
* {
* CExcel.CellsBackColor(excel, ListLayer[0].ListIndex[i] + 2, 1, ListLayer[0].ListIndex[i] + 2, ListLayer.Count + 1, Color.Gold);
* CExcel.CellsBackColor(excel, ListLayer[0].ListIndex[i] + 3, 1, ListLayer[0].ListIndex[i + 1] + 1, ListLayer.Count + 1, CExcel.BlackColor[i]);
* }
*/
// CExcel.ColumnAutoFit(excel, 1, ListLayer.Count + 1);
// CExcel.CellsLineStyle(excel, 1, 1, ListLayer[0].ListIndex[5] + 1, ListLayer.Count + 1);
// CExcel.CellsAlignment(excel, 1, 1, ListLayer[0].ListIndex[5] + 1, 1, Constants.xlRight, Constants.xlCenter);
// CExcel.CellsAlignment(excel, 1, 2, ListLayer[0].ListIndex[5] + 1, ListLayer.Count + 1, Constants.xlCenter, Constants.xlCenter);
if (File.Exists(strName))
{
book.Save();
}
else
{
book.SaveAs(strName, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlShared, miss, miss, miss, miss, miss);
}
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
return(true);
}
catch (Exception ex)
{
excel.Quit();
sMsg = ex.Message;
return(false);
}
}
private void ExportAllNormal(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
object[,] objData;
using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0])
{
if (dt != null && dt.Rows.Count > 0)
{
objData = new Object[dt.Rows.Count, 6];
for (int i = 0; i < dt.Rows.Count; i++)
{
objData[i, 0] = dt.Rows[i][3].ToString();
objData[i, 1] = dt.Rows[i][2].ToString();
objData[i, 2] = dt.Rows[i][0].ToString();
objData[i, 3] = dt.Rows[i][1].ToString();
objData[i, 4] = dt.Rows[i][4].ToString();
objData[i, 5] = dt.Rows[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3", m_objOpt);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, 6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 4).ToString(), m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 4).ToString(), m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 5).ToString(), m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 5).ToString(), m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 6).ToString(), m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 6).ToString(), m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
/// <summary>
/// 通过dataTable把数据库中数据导出到Excel中
/// </summary>
/// <param name="dgv"></param>
/// <param name="sql"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public void DataBaseToExcel1(DataGridView dgv, string sql, string fileName)
{
DataSet ds = new DataSet();
database.ReadDataBase(sql, "historydata", ds);
System.Data.DataTable dtInfo = new System.Data.DataTable();
dtInfo = ds.Tables["historydata"];
if (dtInfo.Rows.Count == 0)
{
MessageBox.Show("没有数据！！！", "提示信息");
return;
}
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel", "警告");
return;
}
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
int countItem = 0; //记录条数
int k = 0;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dtInfo.Rows.Count; i++)
{
countItem = i;
if (countItem % 65535 == 0 && countItem > 0)//一个sheet最多容纳数据条数65535
{
//新加sheet
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(miss, miss, miss, miss);
//生成字段名称
for (int ihead = 0; ihead < dgv.ColumnCount; ihead++)
{
excel.Cells[1, ihead + 1] = dgv.Columns[ihead].HeaderText;
}
k = 0;
}
for (int j = 0; j < dtInfo.Columns.Count; j++)
{
if (dtInfo.Rows[i][j] == typeof(string))
{
worksheet.Cells[k + 2, j + 1] = "'" + dtInfo.Rows[i][j].ToString();
}
else
{
worksheet.Cells[k + 2, j + 1] = dtInfo.Rows[i][j].ToString();
}
}
k++;
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
MessageBox.Show(saveFileName + "文件保存成功", "提示");
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
excel.Quit();
GC.Collect();//垃圾回收
}
private void ExportExcel(DataSet ds)
{
int inHeaderLength = 2, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
//strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName;//Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
int ma = dtbl.Rows.Count;
int na = dtbl.Columns.Count + 2;
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "E" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
na = n;
}
}
//Excel Header
Excel.Range cellRang = excelWorkSheet.get_Range("A1", "E2");
cellRang.Merge(false);
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Gray;
cellRang.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 14;
excelWorkSheet.Cells[1, 1] = "Sales Report of " + DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd");
//excelWorkSheet.Cells[60, 5] = "=SUM(E4,E59)";
//excelWorkSheet.Cells[dtbl.Rows.Count + 2, dtbl.Columns.Count].Formula = a;
//Style table column names
cellRang = excelWorkSheet.get_Range("A3", "E3");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
excelWorkSheet.get_Range("E4").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
//Formate price column
//excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
}
}
